Print

SQL Server Performance Tuning Tips

Posted on August 08, 2016
 

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 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:

  1. Basic query analysis
  2. Advance query analysis
  3. Facilitate tuning by using DB Performance monitoring tool

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.

Tip 1: Know your tables and row counts
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.
Tip 2: Examine the query filters, WHERE and JOIN clauses and note the filtered row count
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.
Tip 3: Know the selectivity of your tables
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.
Tip 4: Analyze the additional query columns
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

Tip 5: Knowing and using constraints can help
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:
Tip 6: Examine the actual execution plan (not the estimated plan)
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.
Tip 7: Record your results, focusing on the number of logical I/Os
If you don’t record the results, you won’t be able to determine the true impact of your changes.
Tip 8: Adjust the query based on your findings and make small, single changes at a time
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.
Tip 9: Re-run the query and record results from the change you made
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.
Tip 10: If you still need more improvement, consider adjusting the indexes to reduce logical I/O
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.
Tip 11: Rerun the query and record results
If you have made adjustments, rerun the query and record those results again.
Tip 12: Engineer out the stupid
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 such as SolarWinds Database Performance Analyzer (DPA) to 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


Learn more about SolarWinds Database Performance Analyzer (DPA).