Something is Using All the CPU. Find It.
The server is under pressure. CPU is high. Disk reads are spiking.
You don’t need to wait for a trace or a profiler session.
The answer is already in sys.dm_exec_query_stats — SQL Server has been keeping score since the last restart.
This query pulls the top offenders by two dimensions:
- Total CPU — which queries have burned the most processor time overall
- Logical reads — which queries are hammering the buffer pool hardest
What This Query Does
sys.dm_exec_query_stats accumulates statistics for every cached query plan.
Combined with sys.dm_exec_sql_text, you get the actual statement text alongside the numbers.
Key columns:
| Column | Meaning |
|---|---|
Total CPU (sec) | Cumulative CPU used by all executions |
Avg CPU (ms) | Per-execution cost — high avg means each run is expensive |
Avg Logical Reads | Buffer pool reads per execution — drives memory pressure |
Avg Physical Reads | Reads that missed cache and hit disk |
Avg Duration (ms) | Wall-clock time per execution |
Plan Compiled | When this plan entered cache — old plans have more data |
Stats reset when plans are evicted from cache (memory pressure,
DBCC FREEPROCCACHE, or restart). Sort byTotal CPUfor cumulative impact,Avg CPUto find queries that are individually expensive regardless of frequency.
Top Queries by Resource Script
Top Queries by Resource.sql
Loading…
Gareth Winterman