Knowing which queries are performing slowly gives you inside knowledge that you can use to optimize SQL Server. Every query that is executed against SQL Server is stored in the plan cache. Besides the raw query and the execution plan that is needed for performance, SQL Server keeps track of statistics about the query. These statistics are used to gauge the performance of the queries.
When a query comes into SQL Server to be executed, SQL Server checks the query against the plan cache to see if it already has generated an execution plan for that query. If the query exists in the plan cache, SQL Server uses the cached execution plan. If the query does not exist in the plan cache, SQL Server generates an execution plan and caches it.
Why does SQL Server cache execution plans and not just generate them every time that a query comes in? Because execution plans are resource intensive to generate, and caching them improves the performance of the execution. Teamcenter executes the same queries repeatedly every day (with different parameters), which makes caching an effective performance benefit.
The plan cache is purged every time that SQL Server is restarted or cycled. The plan cache is not purged when SQL Server is running. There is no maximum size for the plan cache. So, it is not purged because it gets too full. This means that every query that is executed against SQL Server exists in the plan cache, including stored procedures, which are just queries that are saved on the server that is running SQL Server.
The plan cache is the definitive source for all the queries that SQL Server has executed. Conveniently for us, SQL Server keeps performance statistics against the queries that it has executed. Those statistics are retrieved from the sys.dm_exec_query_stats dynamic view. If you execute the following statement, you get a list of all the statistics that are associated with the queries:
SELECT * FROM sys.dm_exec_query_stats
I narrow down the statistics in a couple of ways to find the slowest-performing queries. One way is to order them by the queries that access the hard drive the most and to find the top 10, like this:
SELECT QS.*, ST.text FROM ( SELECT TOP 10 total_physical_reads, total_worker_time/execution_count AS "avg_worder_time", sql_handle FROM sys.dm_exec_query_stats ORDER BY 1 DESC) AS QS CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST
This query returns the following columns:
· total_physical_reads: The total number of physical reads that are performed by executions of this plan since it was compiled.
· avg_worder_time: The average amount of CPU time, in microseconds, that is consumed by executions of this plan since it was compiled.
· sql_handle: A hash that can be passed to sys.dm_exec_sql_text to obtain the text of the cache plan. This can be seen when reading the query above.
· text: The Transact-SQL of the statement.
The following screen shot shows the query results from my test server.
By looking at the top 10 results, I can find the queries that access the hard disk too often and improve their performance. One way to improve their performance is to create a covered index that realigns the data in RAM for the query to access. You can learn more about covered indexes by reading Using a Covered Index on SQLTeam.com. You can learn more about improving the performance of queries on SQL Server by reading Chapter 14 Improving SQL Server Performance on MSDN.
Becoming familiar with the columns that are returned by sys.dm_exec_query_stats will help you find the performance bottlenecks in your SQL Server queries. Find more information about sys.dm_exec_query_stats in the sys.dm_exec_query_stats (Transact-SQL) topic of SQL Server Books Online on MSDN.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic websites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com