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
FrequencyPerSecis expressed in thousands of executions per second, because of the division by 1000.- The division is an integer division: a statement executed fewer than 1,000 times gets 0.
- The frequency is computed over the whole lifetime of the plan, not over a recent window.