SQL Server Top Queries by CPU and IO: dm_exec_query_stats

calendar Apr 4, 2026 / · 6 min read · sql server performance sql scripts sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_query_plan query tuning database administration monitoring execution plan  ·
Share on: linkedin copy

Find the Top CPU and IO Consuming Queries in SQL Server

This script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each.

Purpose and Overview

When a SQL Server instance is under CPU or I/O pressure, the first diagnostic step is finding which queries are responsible. sys.dm_exec_query_stats holds cumulative execution statistics for every query plan currently in the plan cache. Sorted by total logical reads or total CPU time, it quickly surfaces the queries doing the most work. This script, adapted from the DBA-Scripts collection by Bulent Gucuk, combines sys.dm_exec_query_stats with sys.dm_exec_sql_text and sys.dm_exec_query_plan to return the query text and execution plan alongside the performance metrics — giving you everything needed to begin tuning in a single result set.

Code Breakdown

The complete script is shown below, followed by an explanation of each part.

 1SELECT TOP 10
 2    SUBSTRING(
 3        qt.text,
 4        (qs.statement_start_offset / 2) + 1,
 5        (
 6            (CASE qs.statement_end_offset
 7                WHEN -1 THEN DATALENGTH(qt.text)
 8                ELSE qs.statement_end_offset
 9            END - qs.statement_start_offset) / 2
10        ) + 1
11    )                                           AS statement_text,
12    qs.execution_count,
13    qs.total_logical_reads,
14    qs.last_logical_reads,
15    qs.total_logical_writes,
16    qs.last_logical_writes,
17    qs.total_worker_time,
18    qs.last_worker_time,
19    qs.total_elapsed_time / 1000000            AS total_elapsed_time_s,
20    qs.last_elapsed_time / 1000000             AS last_elapsed_time_s,
21    qs.last_execution_time,
22    qp.query_plan
23FROM sys.dm_exec_query_stats AS qs
24CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
25CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
26ORDER BY qs.total_logical_reads DESC;    -- sort by IO
27-- ORDER BY qs.total_worker_time DESC;  -- sort by CPU
28-- ORDER BY qs.total_elapsed_time DESC; -- sort by total duration

sys.dm_exec_query_stats

sys.dm_exec_query_stats holds one row per query plan in the plan cache. Each row accumulates statistics across all executions of that plan since it was compiled and cached. Key columns:

  • execution_count — how many times this plan has executed since it was cached; divide total metrics by this to get per-execution averages
  • total_logical_reads — cumulative logical read operations across all executions; high values indicate heavy table or index scan activity
  • last_logical_reads — logical reads for the most recent single execution; useful for spotting sudden spikes
  • total_logical_writes — cumulative writes; elevated values often point to sort spills into tempdb or large bulk operations
  • total_worker_time — cumulative CPU time in microseconds across all executions; divide by 1,000,000 to convert to seconds
  • total_elapsed_time — cumulative wall-clock time in microseconds; includes wait time in addition to CPU time
  • last_execution_time — when the plan last executed; helps confirm whether a high-impact query is currently active or historical

CROSS APPLY sys.dm_exec_sql_text

sys.dm_exec_sql_text accepts a sql_handle and returns the full batch text. The SUBSTRING expression extracts just the specific statement within the batch rather than the entire batch:

  • statement_start_offset and statement_end_offset are byte offsets into the batch text
  • Dividing by 2 converts from byte offset to character position for Unicode strings
  • When statement_end_offset is -1, the statement runs to the end of the batch, so DATALENGTH(qt.text) is used as the upper bound

This is important because a stored procedure with ten statements generates one row per statement in sys.dm_exec_query_stats. Without the SUBSTRING, you would see the entire procedure body for every row, making it hard to identify which statement is the expensive one.

CROSS APPLY sys.dm_exec_query_plan

sys.dm_exec_query_plan accepts a plan_handle and returns the XML execution plan in the query_plan column. In SQL Server Management Studio, clicking this column opens the graphical execution plan, which shows operator costs, estimated vs. actual row counts, and index usage — all essential for diagnosing why a query is expensive.

Sorting by IO vs CPU

The script defaults to ORDER BY qs.total_logical_reads DESC to rank queries by cumulative I/O impact. Swap to the commented alternatives to rank by CPU (total_worker_time) or total elapsed time. Running the query both ways is worthwhile — the worst I/O consumer and the worst CPU consumer are often different queries.

Key Benefits and Use Cases

  • Identifies the top resource consumers in the plan cache without needing Profiler or Extended Events
  • Returns query text and execution plan in the same result set for immediate investigation
  • Works on all SQL Server editions and versions — reads only from built-in DMVs
  • execution_count allows normalizing totals to per-execution averages to distinguish one very bad query from a moderate query called many times
  • Three sort options (IO, CPU, elapsed time) cover the most common performance investigation angles
  • last_execution_time confirms whether a high-impact query is still running frequently

Performance Considerations

  • Plan cache scope: sys.dm_exec_query_stats only reflects queries whose plans are currently cached. Plans are evicted under memory pressure, after an explicit DBCC FREEPROCCACHE, or when the underlying objects change. A query that ran intensively last night but is not in cache now will not appear.
  • Cumulative vs. per-execution: total_logical_reads accumulates since the plan was cached, so a query run 10,000 times with modest I/O each time can rank above a single catastrophic query. Always check execution_count and compute per-execution averages: total_logical_reads / execution_count.
  • Ad hoc queries: Environments with optimize for ad hoc workloads disabled may have thousands of single-use plans in cache. Filter by execution_count > 1 to focus on repeated queries.
  • Query Store: SQL Server 2016 and later includes Query Store, which persists historical query statistics across restarts and plan cache flushes. For trend analysis over days or weeks, Query Store is more reliable than DMV queries.
  • Required permissions: VIEW SERVER STATE is required to query sys.dm_exec_query_stats.

Practical Tips

To compute per-execution averages alongside totals, extend the SELECT list:

 1SELECT TOP 10
 2    SUBSTRING(qt.text,
 3        (qs.statement_start_offset / 2) + 1,
 4        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text)
 5          ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1
 6    )                                                  AS statement_text,
 7    qs.execution_count,
 8    qs.total_logical_reads,
 9    qs.total_logical_reads / qs.execution_count        AS avg_logical_reads,
10    qs.total_worker_time / 1000000                     AS total_cpu_s,
11    qs.total_worker_time / qs.execution_count / 1000   AS avg_cpu_ms,
12    qs.total_elapsed_time / 1000000                    AS total_elapsed_s,
13    qs.total_elapsed_time / qs.execution_count / 1000  AS avg_elapsed_ms,
14    qs.last_execution_time,
15    qp.query_plan
16FROM sys.dm_exec_query_stats AS qs
17CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
18CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
19ORDER BY qs.total_logical_reads DESC;

To scope results to a specific database, add a filter on the database name extracted from the SQL text, or join to sys.dm_exec_plan_attributes to filter by database ID:

1CROSS APPLY sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
2WHERE pa.attribute = 'dbid'
3    AND pa.value = DB_ID('YourDatabase')

Run this script at the start of any performance investigation. The top five queries by total logical reads account for the majority of I/O on most SQL Server instances. Focus tuning effort on those before looking elsewhere.

Conclusion

sys.dm_exec_query_stats is one of the most useful DMVs for SQL Server performance work. Combined with sys.dm_exec_sql_text to retrieve query text and sys.dm_exec_query_plan to retrieve the execution plan, this script surfaces the most expensive queries in the plan cache in seconds. Sort by logical reads to find I/O bottlenecks, by worker time to find CPU consumers, or by elapsed time to find queries that take longest from the user's perspective. Add per-execution averages to distinguish high-volume moderate queries from low-volume catastrophic ones, and follow up in Query Store for historical analysis that survives plan cache eviction.

References

Posts in this series