SQL SERVER – High Frequency Cached Query Counts and Statement Metrics

I measure High Frequency cached statements with decimal executions-per-second arithmetic. A customer wanted to identify the busiest queries.

SELECT TOP (20)
 SUBSTRING(t.text,(s.statement_start_offset/2)+1,
  ((CASE s.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
       ELSE s.statement_end_offset END-s.statement_start_offset)/2)+1) AS StatementText,
 s.execution_count,s.creation_time,
 s.max_elapsed_time/1000.0 AS MaxElapsedMs,
 s.total_elapsed_time/1000.0/NULLIF(s.execution_count,0) AS AverageElapsedMs,
 s.execution_count*1.0/NULLIF(DATEDIFF_BIG(millisecond,s.creation_time,SYSDATETIME())/1000.0,0) AS ExecutionsPerSecond
FROM sys.dm_exec_query_stats s CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) t
ORDER BY ExecutionsPerSecond DESC;

My old formula divided execution_count by 1000 before dividing by seconds. Counts are not milliseconds, and integer division erased useful rates. This calculation divides executions by elapsed seconds since cache-entry creation.

Elapsed-time fields convert microseconds to milliseconds. Statement offsets isolate the individual statement instead of labeling the batch as one query. DATEDIFF_BIG requires SQL Server 2016 or later.

Eviction, recompilation and separate cached plans split or remove observations. A newly created entry can have no measurable interval, producing NULL. The average does not represent complete all-time history.

Use controlled deltas or retained workload evidence for a particular busy minute. Fetch plans for selected candidates. Retrieving every XML plan adds avoidable collection cost.

Related reading

• Comprehensive Database Performance Health Check
• SQL SERVER – Finding The Oldest Query Plan From Cache
• SQL in Sixty Seconds
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

A cache-lifetime rate is not an instantaneous rate, it is an average over the retained entry interval.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.