Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: PerformanceDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Find active requests using the most CPU
  5. 2. Show the currently executing statement inside the batch
  6. 3. Find cached queries by total CPU
  7. 4. Find queries with the highest average CPU per execution
  8. 5. Find high-frequency CPU consumers
  9. 6. Group logically equivalent queries by query hash
  10. 7. Show query text and execution plan for high-CPU cached queries
  11. 8. Compare CPU time with elapsed time for active requests
  12. 9. Check whether CPU pressure is broader than one query
  13. 10. Use Query Store for historical high CPU
  14. What the result means
  15. What to look for
  16. High total CPU + high execution count
  17. High average CPU + low execution count
  18. High CPU + high logical reads
  19. High elapsed time + low CPU
  20. Many logically equivalent ad hoc queries
  21. High CPU + runnable scheduler queues
  22. Production notes
  23. sys.dm_exec_query_stats is cache-based
  24. Query stats update after execution completes
  25. CPU units differ between DMVs
  26. Parallel queries complicate interpretation
  27. Cached averages can hide skew
  28. Permissions vary by version
  29. High CPU can be legitimate
  30. Common mistakes
  31. 1. Sorting only by average CPU
  32. 2. Sorting only by total CPU
  33. 3. Treating elapsed time as CPU time
  34. 4. Using sys.dm_exec_query_stats for currently running queries
  35. 5. Ignoring plan-cache lifecycle
  36. 6. Assuming high logical reads automatically mean missing index
  37. 7. Changing MAXDOP because CPU is high
  38. 8. Ignoring application call frequency
  39. Next step
  40. 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.total_worker_time 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_time
  • max_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.

Next step

Use the CPU pattern to choose the next investigation.

  • High active CPU on one request → inspect its current statement and execution plan.
  • High total CPU + high execution count → inspect call frequency and optimize the hot path.
  • High average CPU + low execution count → inspect the individual query plan and data access pattern.
  • High CPU + high reads → investigate scans, joins, estimates, and indexing.
  • High elapsed time + low CPU → switch to wait and blocking analysis.
  • Many ad hoc variants → group by query_hash and investigate parameterization/query generation.
  • Live DMV no longer shows the problem → use Query Store.
  • Scheduler runnable queues stay elevated → investigate system-wide CPU pressure and concurrency.

← All cheat sheets