LLI Wiki — My Digital Knowledge Base

SQL Server: high-frequency queries from the plan cache

Rank the cached statements by execution frequency, with their average elapsed time and plan.

This query ranks the statements of the plan cache by execution frequency, computed over the lifetime of the plan (sys.dm_exec_query_stats). It also shows the execution count, the average and maximum elapsed time, and the plan.

Query

SELECT
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / 1000 /
NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
s.creation_time AS CreationTime,
ISNULL(s.execution_count / 1000 /
NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
,query_plan
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
ORDER BY FrequencyPerSec DESC

Notes

Mastodon LLIMastodon SocialFosstodon