List the most expensive cached statements from query stats
Query sys.dm_exec_query_stats and sys.dm_exec_sql_text to list cached statements and stored procedures by logical reads, with execution counts and calls per second.
The plan cache keeps execution statistics for each cached statement. The query below lists, for the whole instance, the cached statements that have a database context, with their execution count, average and total CPU time, total logical reads and calls per second. The stored procedure name is filled in only when the statement belongs to a module.
SELECT DB_NAME(qt.[dbid]) AS 'DatabaseName', OBJECT_SCHEMA_NAME(qt.[objectid], qt.[dbid]) AS 'SchemaName', OBJECT_NAME(qt.[objectid], qt.[dbid]) AS 'SPName', qs.execution_count AS 'Execution Count', qs.total_worker_time/qs.execution_count AS 'AvgWorkerTime', qs.total_worker_time AS 'TotalWorkerTime', qs.total_logical_reads AS 'TotalLogicalReads', qs.execution_count/DATEDIFF(Second, qs.creation_time, GETDATE()) AS 'Calls/Second', qs.creation_time 'CreationTime' FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE DB_NAME(qt.[dbid]) IS NOT NULL --remove to see all queries ORDER BY qs.total_logical_reads DESC
Notes
- The statistics cover the lifetime of each cached plan. They are lost when the plan leaves the cache.
Calls/Secondis the execution count divided by the seconds since the plan was created. For a plan created in the same second as the query,DATEDIFFreturns 0 and the query raises a divide-by-zero error. The query is kept as in the source.- Remove the
WHERE DB_NAME(qt.[dbid]) IS NOT NULLfilter to include the statements without a database context, as the comment in the query says.