Diagnosing Common Query Performance Problems with Execution Plans

Tony Davis
Application & Database Development
Ever scratched your head in bemusement at how an apparently 'simple' T-SQL query could perform so poorly or erratically? SQL Server's execution plan for the query will likely reveal the root cause. It will tell you exactly which tables and indexes SQL Server accessed, and what operations it performed, in order to return the data that your query needed. If your query logic confused the optimizer, or if it had inaccurate statistical knowledge of the data, then prepare for some nasty surprises in the plan, such as expensive scans, sort and join operations that spill to disk, and more. We'll examine common causes of these problems, such as use of 'generic' SQL, parameter sniffing, stale statistics, and misuse of functions. We'll cover techniques that will allow you to locate quickly the execution plans for high-cost queries in the workload, and we'll use some of the new execution plan features in SQL Server 2016 to detect and diagnose more easily the underlying query performance issues.

Accompanying Material

No material found.

Back to Top cage-aids