LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon