Browse cheat sheets · SQL Server
Find High CPU Queries
Identify SQL Server queries consuming the most CPU now and historically, distinguish total CPU from CPU per execution, and choose the right next tuning step.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Find active requests using the most CPU
- 2. Show the currently executing statement inside the batch
- 3. Find cached queries by total CPU
- 4. Find queries with the highest average CPU per execution
- 5. Find high-frequency CPU consumers
- 6. Group logically equivalent queries by query hash
- 7. Show query text and execution plan for high-CPU cached queries
- 8. Compare CPU time with elapsed time for active requests
- 9. Check whether CPU pressure is broader than one query
- 10. Use Query Store for historical high CPU
- What the result means
- What to look for
- High total CPU + high execution count
- High average CPU + low execution count
- High CPU + high logical reads
- High elapsed time + low CPU
- Many logically equivalent ad hoc queries
- High CPU + runnable scheduler queues
- Production notes
- sys.dm_exec_query_stats is cache-based
- Query stats update after execution completes
- CPU units differ between DMVs
- Parallel queries complicate interpretation
- Cached averages can hide skew
- Permissions vary by version
- High CPU can be legitimate
- Common mistakes
- 1. Sorting only by average CPU
- 2. Sorting only by total CPU
- 3. Treating elapsed time as CPU time
- 4. Using sys.dm_exec_query_stats for currently running queries
- 5. Ignoring plan-cache lifecycle
- 6. Assuming high logical reads automatically mean missing index
- 7. Changing MAXDOP because CPU is high
- 8. Ignoring application call frequency
- Next step
- Related deep dives
Problem
SQL Server CPU is elevated, application latency is increasing, or you need to identify which queries are responsible for the most processor time.
The goal is to answer:
- Which requests are consuming CPU right now?
- Which cached queries have consumed the most total CPU?
- Which queries are expensive per execution?
- Is one query expensive, or is a very frequent query creating aggregate CPU pressure?
- Is high elapsed time really CPU-bound, or is the query mostly waiting?
Quick answer
For current CPU activity, start with sys.dm_exec_requests.
SELECT TOP (20)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.cpu_time AS cpu_ms,
CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
r.logical_reads,
r.wait_type,
r.blocking_session_id,
s.login_name,
s.host_name,
s.program_name,
txt.text AS batch_text
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
r.session_id <> @@SPID
AND s.is_user_process = 1
ORDER BY
r.cpu_time DESC;
For recent completed workload in the plan cache, use sys.dm_exec_query_stats.
SELECT TOP (20)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.total_worker_time DESC;
Use both views together:
- active request data tells you what is happening now
- query stats tell you what recently completed workload consumed CPU
Code / commands
1. Find active requests using the most CPU
SELECT TOP (20)
r.session_id,
DB_NAME(r.database_id) AS database_name,
r.status,
r.command,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
r.logical_reads,
r.reads,
r.writes,
r.wait_type,
r.blocking_session_id,
txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
r.session_id <> @@SPID
ORDER BY
r.cpu_time DESC;
Use this during an active incident.
cpu_time is CPU milliseconds used by the current request.
A high value does not automatically mean the request is the root cause, but it is a strong lead when server CPU is also high.
2. Show the currently executing statement inside the batch
A batch can contain many statements. Use statement offsets to isolate the one currently running.
SELECT TOP (20)
r.session_id,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
r.logical_reads,
CASE
WHEN r.statement_start_offset IS NULL THEN txt.text
ELSE SUBSTRING(
txt.text,
(r.statement_start_offset / 2) + 1,
(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(txt.text)
ELSE r.statement_end_offset
END
- r.statement_start_offset
) / 2
) + 1
)
END AS statement_text,
txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
r.session_id <> @@SPID
ORDER BY
r.cpu_time DESC;
This is usually more actionable than reviewing an entire stored procedure or multi-statement batch.
3. Find cached queries by total CPU
Use this to identify workload that has consumed the most aggregate CPU since the cached plan was compiled.
SELECT TOP (50)
qs.execution_count,
qs.creation_time,
qs.last_execution_time,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(qs.last_worker_time / 1000.0 AS decimal(18,2)) AS last_cpu_ms,
qs.total_logical_reads,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY
qs.total_worker_time DESC;
This is usually the right query when the question is:
Which cached query workload has consumed the most CPU overall?
4. Find queries with the highest average CPU per execution
SELECT TOP (50)
qs.execution_count,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(qs.max_worker_time / 1000.0 AS decimal(18,2)) AS max_cpu_ms,
CAST(qs.min_worker_time / 1000.0 AS decimal(18,2)) AS min_cpu_ms,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
qs.execution_count > 0
ORDER BY
avg_cpu_ms DESC;
Use this to find expensive individual executions.
Always review execution_count next to the average.
One execution with 20 seconds of CPU may matter less than a 20 ms query executed a million times.
5. Find high-frequency CPU consumers
SELECT TOP (50)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
qs.last_execution_time,
txt.text AS batch_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
WHERE
qs.execution_count >= 100
ORDER BY
qs.total_worker_time DESC;
The 100 is only an example filter for reducing output.
It is not a universal threshold.
Adjust it to the workload.
6. Group logically equivalent queries by query hash
Ad hoc literals can create multiple cache entries for logically equivalent statements.
Grouping by query_hash can reveal aggregate CPU more clearly.
SELECT TOP (50)
qs.query_hash,
SUM(qs.execution_count) AS executions,
CAST(
SUM(qs.total_worker_time) / 1000.0
AS decimal(18,2)
) AS total_cpu_ms,
CAST(
SUM(qs.total_worker_time) * 1.0
/ NULLIF(SUM(qs.execution_count), 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
MAX(qs.last_execution_time) AS last_execution_time
FROM sys.dm_exec_query_stats AS qs
GROUP BY
qs.query_hash
ORDER BY
total_cpu_ms DESC;
Use this when literal differences make the cache look fragmented.
Then drill into the query text for the relevant hash.
7. Show query text and execution plan for high-CPU cached queries
SELECT TOP (20)
qs.execution_count,
CAST(qs.total_worker_time / 1000.0 AS decimal(18,2)) AS total_cpu_ms,
CAST(
qs.total_worker_time * 1.0
/ NULLIF(qs.execution_count, 0)
/ 1000.0
AS decimal(18,2)
) AS avg_cpu_ms,
txt.text AS batch_text,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY
qs.total_worker_time DESC;
Use the plan to investigate:
- large scans
- expensive joins
- sorts
- hash operations
- poor estimates
- repeated lookups
- parallelism
- spills or memory pressure indicators
Do not optimize a plan based on one operator alone.
8. Compare CPU time with elapsed time for active requests
SELECT
r.session_id,
r.cpu_time AS cpu_ms,
r.total_elapsed_time AS elapsed_ms,
CAST(
100.0 * r.cpu_time
/ NULLIF(r.total_elapsed_time, 0)
AS decimal(6,2)
) AS cpu_to_elapsed_pct,
r.status,
r.wait_type,
r.blocking_session_id,
txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
r.session_id <> @@SPID
ORDER BY
r.cpu_time DESC;
This ratio is only a rough triage signal.
Elapsed time includes waiting, and parallel work can complicate interpretation.
Use it to ask:
- Is the request mostly doing CPU work?
- Or is it mostly waiting?
Do not use the percentage as a hard classification threshold.
9. Check whether CPU pressure is broader than one query
CPU-heavy queries matter more when the scheduler is under pressure.
SELECT
scheduler_id,
cpu_id,
status,
is_online,
current_tasks_count,
runnable_tasks_count,
active_workers_count,
current_workers_count,
load_factor
FROM sys.dm_os_schedulers
WHERE
status = N'VISIBLE ONLINE'
ORDER BY
scheduler_id;
runnable_tasks_count represents workers ready to run but waiting for scheduler CPU.
Repeated nonzero runnable queues during high CPU can support a CPU-pressure diagnosis.
Do not use a single instantaneous sample as a universal threshold.
10. Use Query Store for historical high CPU
If the expensive query has already completed or plan cache data is insufficient, Query Store is usually the better historical source.
SELECT TOP (20)
q.query_id,
p.plan_id,
qt.query_sql_text,
SUM(rs.count_executions) AS executions,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ 1000.0
AS decimal(18,2)
) AS total_cpu_ms,
CAST(
SUM(rs.avg_cpu_time * rs.count_executions)
/ NULLIF(SUM(rs.count_executions), 0)
/ 1000.0
AS decimal(18,2)
) AS weighted_avg_cpu_ms
FROM sys.query_store_query_text AS qt
INNER JOIN sys.query_store_query AS q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.query_store_plan AS p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_runtime_stats AS rs
ON rs.plan_id = p.plan_id
GROUP BY
q.query_id,
p.plan_id,
qt.query_sql_text
ORDER BY
total_cpu_ms DESC;
This is the better path for:
- historical regressions
- plan comparison
- workload before/after analysis
- queries no longer present in plan cache
What the result means
| Field | Why it matters |
|---|---|
cpu_time |
CPU milliseconds consumed by an active request. |
total_worker_time |
Total CPU time accumulated by completed executions represented by the cached query stats row. |
last_worker_time |
CPU consumed by the most recently completed execution represented by the stats row. |
min_worker_time |
Lowest CPU time observed for one execution in the current cache lifecycle. |
max_worker_time |
Highest CPU time observed for one execution in the current cache lifecycle. |
execution_count |
Number of completed executions represented by the cached stats row. |
avg_cpu_ms |
Derived CPU per execution. Useful only with execution count and workload context. |
query_hash |
Hash that can help group logically equivalent queries. |
total_elapsed_time |
Wall-clock elapsed time, including waiting. |
runnable_tasks_count |
Scheduler workers ready to run but waiting for CPU. |
What to look for
High total CPU + high execution count
This often means a “small but frequent” query is consuming a large share of CPU.
Potential next steps:
- reduce execution frequency
- batch work
- cache results
- improve access path
- reduce repeated application calls
High average CPU + low execution count
This usually points toward an individually expensive operation.
Inspect:
- execution plan
- scans
- joins
- sorts
- aggregations
- cardinality estimates
- parameter behavior
High CPU + high logical reads
This often points to inefficient data access.
Possible causes:
- large scans
- weak predicates
- poor join strategy
- missing or ineffective indexing
- bad estimates
Do not assume the fix is an index until the plan supports that conclusion.
High elapsed time + low CPU
The query may not be CPU-bound.
Look at:
- waits
- blocking
- I/O
- memory grants
- client/network consumption
Many logically equivalent ad hoc queries
Grouping by query_hash can reveal that the workload is larger than any one cache row suggests.
This may point toward parameterization or application query-shape issues.
High CPU + runnable scheduler queues
This strengthens the case for actual CPU pressure.
Next, determine whether pressure comes from:
- one query
- many concurrent queries
- parallelism
- compilation
- application traffic growth
Production notes
sys.dm_exec_query_stats is cache-based
The DMV contains statistics for cached query plans.
When the plan leaves cache, the row disappears.
This means the DMV is useful for recent workload analysis but is not durable historical telemetry.
Query stats update after execution completes
sys.dm_exec_query_stats reflects completed query executions.
For a query still running, use sys.dm_exec_requests.
CPU units differ between DMVs
sys.dm_exec_requests.cpu_time is milliseconds.
sys.dm_exec_query_stats is reported in microseconds, though the underlying accuracy is only to milliseconds.
Convert units before comparing values.
Parallel queries complicate interpretation
CPU time can exceed elapsed wall-clock time when work is performed in parallel across multiple workers.
Do not assume cpu_time > elapsed_time is an error.
Cached averages can hide skew
One query may be cheap for most parameter values and very expensive for a few.
Use:
min_worker_timemax_worker_time- Query Store
- plan history
- parameter-sensitive analysis
when distribution matters.
Permissions vary by version
For broad access to these performance DMVs:
- SQL Server 2019 and earlier generally require
VIEW SERVER STATE - SQL Server 2022 and later require
VIEW SERVER PERFORMANCE STATE
Azure SQL Database has different visibility and permission rules.
High CPU can be legitimate
A reporting query or batch process may intentionally consume significant CPU.
The question is not only:
Is CPU high?
Also ask:
Is CPU use expected, efficient, and within the workload SLA?
Common mistakes
1. Sorting only by average CPU
A one-time expensive query can dominate averages while contributing little total load.
Always compare average and total CPU.
2. Sorting only by total CPU
A high-frequency query may dominate total CPU but still be individually efficient.
Sometimes the best fix is reducing call volume, not rewriting the SQL.
3. Treating elapsed time as CPU time
Elapsed time includes waiting.
Use cpu_time, worker_time, and waits separately.
4. Using sys.dm_exec_query_stats for currently running queries
Its statistics update when execution completes.
Use sys.dm_exec_requests for in-flight work.
5. Ignoring plan-cache lifecycle
A recent restart, recompilation, or plan eviction shortens the observation window.
Do not compare cache totals without context.
6. Assuming high logical reads automatically mean missing index
Reads are evidence of work, not a diagnosis.
Check the actual execution plan and workload.
7. Changing MAXDOP because CPU is high
Parallelism may be helping the query finish faster.
Investigate plans, scheduler pressure, concurrency, and workload before changing instance-level settings.
8. Ignoring application call frequency
A query can be perfectly reasonable in isolation but become expensive when called thousands of times per second.
CPU troubleshooting must include workload volume.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.