As you’re doubtless aware, one of SQL Server’s biggest jobs in terms of day-to-day work is analyzing queries submitted...
to the server and figuring out how to execute them. What indexes will be used? What tables will merely be scanned? In what order will these various operations occur to create the fastest-possible query?
This work is done by SQL Server’s Query Optimizer, and the result is a query execution plan, or just “execution plan.” SQL Server will cache these plans so that they can be re-used in the near future if the same query is submitted again.
Being able to interpret these plans can be a powerful way to fine-tune query execution times, and to spot specific problem situations that you can then fix. First, you have to get to the query execution plan. The easiest way is to copy and paste a query into the SQL Server Management Studio and execute it with the “Show Execution Plan” option turned on (don’t bother with the “Estimated Execution Plan” unless it would be a “Bad Thing” to actually run the query; the estimated query execution plan isn’t always the same as the final selected plan, but it can be retrieved without running the query).
Query execution plan analysis is a deep skill set, and you’ll find a good introduction in this tuning tutorial. However, there are a few general guidelines you can stick to:
- Index and table scans are Bad Things; you want to see seeks, not scans. Scans indicate that SQL wasn’t able to find a useful index. It could be that you need better indexes, or that your indexes are too fragmented to be useful -- rebuild them.
- Bookmark lookups can be time-consuming; consider changing the column that is the table’s clustered index, or consider building covering indexes that contain all of the data the query needs.
- Filter: Try to avoid any T-SQL functions in WHERE clauses, such as WHERE TOLOWER(Name) LIKE '%jones'. These require a lot of extra work for SQL Server.
- Sort: Ideally, whatever you’re sorting on will already be sorted in an index, which is very fast. If you’re sorting on an un-indexed field, expect poorer performance.
Of course, every corrective action you take can have a corresponding downside, so don’t do anything based on a single query’s execution plan. It’s often better to capture several queries and run them through the database tuning advisor, which incorporates a lot of this query optimization knowledge and can access the Query Optimizer engine directly to generate estimated query execution plans.
ABOUT THE AUTHOR
Don Jones is a co-founder of Concentrated Technology LLC, the author of more than 30 IT books and a speaker at technical conferences worldwide. Contact him through his website at www.ConcentratedTech.com.