DBAtlas · Database operations atlas

Find the most expensive queries in the plan cache (CPU, reads, duration)

SQL ServerPerformance

Command

SELECT TOP (20)
       qs.query_hash,
       SUM(qs.execution_count)                                       AS execs,
       SUM(qs.total_worker_time) / 1000                              AS total_cpu_ms,
       SUM(qs.total_worker_time) / SUM(qs.execution_count) / 1000    AS avg_cpu_ms,
       SUM(qs.total_elapsed_time) / SUM(qs.execution_count) / 1000   AS avg_ms,
       SUM(qs.total_logical_reads) / SUM(qs.execution_count)         AS avg_reads,
       SUM(qs.total_grant_kb) / SUM(qs.execution_count)              AS avg_grant_kb,
       COUNT(DISTINCT qs.query_plan_hash)                            AS plan_count,
       MAX(qs.last_execution_time)                                   AS last_exec,
       MIN(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
           ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1)) AS statement_text,
       MIN(DB_NAME(pa.dbid))                                         AS db
FROM   sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  OUTER APPLY (SELECT CONVERT(int, value) AS dbid FROM sys.dm_exec_plan_attributes(qs.plan_handle)
               WHERE attribute = N'dbid') pa
GROUP BY qs.query_hash
ORDER BY total_cpu_ms DESC;

-- To rank by reads: ORDER BY SUM(qs.total_logical_reads) DESC
-- Plan: SELECT query_plan FROM sys.dm_exec_query_plan(<plan_handle>);

What it does

Groups statements in the plan cache by query_hash (ad-hoc queries that differ only in literals collapse into one row) and returns total and average CPU, duration, logical reads, memory grant and plan count.

When to use it

When CPU is pegged, on a "server is slow" complaint, for databases without Query Store, or when you need a server-wide view across all databases.

Caution

Only sees plans still in cache: queries whose plans were evicted by restart, DBCC FREEPROCCACHE, memory pressure or RECOMPILE are missing. Use Query Store for persistent, time-windowed analysis. plan_count > 1 hints at parameter sensitivity or different SET options. On a very large plan cache the query can take a few seconds; safe.

Open in DBAtlas →

Related