Query tuning is often the fastest way to accelerate SQL Server performance. Most often system-level server performance (memory, processors, and so on) improvement measures are ineffective and expensive. Expert developers believe most performance issues can be traced to poorly written queries & inefficient indexing, not hardware constraints. In fact, some performance issues can only be resolved through query tuning.
But when it comes to SQL Server performance query tuning, DBAs often grapple with the question of where to start. How do you assess a query? How can you discover flaws in the way a query was written? How can you uncover hidden opportunities for improvement? Query tuning is half science and also, half art as there are no right or wrong answers, only the most appropriate solution for a given situation.
SQL Server query tuning can be categorized into three broad steps:
Here are 12 quick tips that can help a DBA improve query performance in a measurable way and at the same time provide certainty that the specific alteration has actually improved the speed of the query.
1. Basic query analysis
DBAs need visibility into all layers and information on expensive queries in order to isolate the root cause. Effective tuning requires knowing top SQL statements, top wait types, SQL plans, blocked queries, resource contention, and the effect of missing indexes. Start with the basics—knowing exactly what you’re dealing with before you dive in can help.
First, make sure you are actually operating on a table, not view or table-valued function. Table-valued functions have their own performance implications. You can use SSMS to hover over query elements to examine these details. Check the row count by querying the DMVs.
If there are no filters, and the majority of table is returned, consider whether all that data is needed. If there are no filters at all, this could be a red flag and warrants further investigation. This can really slow a query down.
Based upon the tables and the filters in the previous two tips , know how many rows you’ll be working with, or the size of the actual, logical set. We recommend the use of SQL diagramming as a powerful tool in assessing queries and query selectivity.
Examine closely the SELECT * or scalar functions to determine whether extra columns are involved. The more columns you bring back, the less optimal it may become for an execution plan to use certain index operations, and this can, in turn, degrade performance.
2. Advanced query analysis
Knowing and using constraints can be helpful as you start to tune. Review the existing keys, constraints, indexes to make sure you avoid duplication of effort or overlapping of indexes that already exist.To get information about your indexes, run the sp_helpindex stored procedure:
Estimated plans use estimated statistics to determine the estimated rows; actual plans use actual statistics at runtime. If the actual and estimated plans are different, you may need to investigate further.
If you don’t record the results, you won’t be able to determine the true impact of your changes.
Making too many changes at one time can be ineffective as they can cancel each other out! Begin by looking for the most expensive operations first. There is no right or wrong answer, but only what is optimal for the given situation.
If you see an improvement in logical I/Os, but the improvement isn’t enough, return to tip 8 to examine other factors that may need adjusting. Keep making one change at a time, rerun the query and comparing results until you are satisfied that you have addressed all the expensive operations that you can.
Adding or adjusting indexes isn’t always the best thing to do, but if you can’t alter the code, it may be the only thing you can do. You can consider the existing indexes, a covering index and a filtered index for improvements.
If you have made adjustments, rerun the query and record those results again.
Lookout for frequently encountered inhibitors of performance like: code first generators, abuse of wildcards, scalar functions, Nested views, cursors and row by row processing.
3. Use a DB Performance monitoring tool to facilitate query tuning.
Traditional database monitoring tools focus on health metrics. Current application performance management tools provide hints, but do not help find the root cause.
Tip: You can make query tuning significantly easier by using a continuous database performance monitoring solution. Recommendations of database performance monitoring tools include:
SolarWinds® Database Performance Analyzer (DPA)
SolarWinds Database Performance Analyzer (DPA) can consolidate performance information in a single place. With DPA, you can:
– Identify the specific query that got delayed
– Identify the specific bottleneck (wait event) that causes a delay
– Show the time impact of the identified bottleneck
SolarWinds® SQL Sentry
SQL Sentry is another SQL Server performance tuning tool that can help you tackle performance issues. Using SQL Sentry, you can review historical performance and detect issues to begin troubleshooting faster.
SQL Sentry features include:
- Gain insight into past SQL Server performance – Go back in time to see what changed
- View Top SQL query, blocking, and deadlock analysis – See specific moments to better understand what queries are running and analyze blocking details
- Optimize queries – Using the built-in Plan Explorer tool, you can more easily analyze query stats and view query plan diagrams
Download a free 14-day trial of SQL Sentry here.