Find SQL Queries Causing High CPU Explained with Examples

Scenario

SQL Server CPU suddenly reaches 100%. The goal is to identify whether SQL Server is responsible and find the queries consuming the most CPU.

1. Check currently running high-CPU queries

SELECT
    r.session_id,
    r.status,
    r.cpu_time,
    r.total_elapsed_time,
    r.logical_reads,
    r.blocking_session_id,
    DB_NAME(r.database_id) AS DatabaseName,
    t.text AS QueryText
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

Look for queries with:

High cpu_time
High logical reads
Long elapsed time
Repeated or parallel execution

2. Find cached queries with the highest total CPU

SELECT TOP (10)
    qs.execution_count,
    qs.total_worker_time / 1000 AS TotalCpuMs,
    qs.total_worker_time /
        NULLIF(qs.execution_count, 0) / 1000 AS AverageCpuMs,
    qs.total_logical_reads,
    qs.total_elapsed_time / 1000 AS TotalElapsedMs,
    st.text AS QueryText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

TotalCpuMs: queries consuming the most CPU overall
AverageCpuMs: expensive individual executions
execution_count: frequently executed queries

A small query executed thousands of times can consume more CPU than one large query.

3. Use Query Store

Query Store provides historical information even after the CPU spike ends.

Check:

Top queries by CPU
Execution-count increases
Recent execution-plan changes
Plan regression
CPU usage by time interval

This helps answer:

Which query started consuming more CPU when the problem began?

4. Examine the execution plan

Check the responsible queries for:

Table or index scans
Missing indexes
High logical reads
Incorrect row estimates
Expensive joins, sorting or aggregation
Scalar functions
Implicit conversions
Parameter sniffing
Excessive parallelism

5. Check CPU-related waits

SELECT TOP (10)
    wait_type,
    waiting_tasks_count,
    wait_time_ms,
    signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY signal_wait_time_ms DESC;

High signal wait time can indicate that runnable tasks are waiting for CPU.

SOS_SCHEDULER_YIELD commonly indicates CPU pressure, but it must be interpreted with other metrics.

6. Check recent changes

Investigate whether the spike started after:

Application deployment
Stored-procedure modification
Index removal
Statistics changes
Plan regression
Increased traffic
Scheduled reports or jobs
ETL or maintenance tasks

Possible Solutions

Depending on the root cause:

Optimize the query
Add or improve indexes
Update statistics
Fix parameter-sensitive plans
Reduce unnecessary executions
Remove row-by-row processing
Cache repeatedly requested data
Review parallelism settings
Reschedule heavy jobs
Scale CPU only after query optimization

Interview Answer

I would first use sys.dm_exec_requests to identify currently running sessions with high CPU time. Then I would use sys.dm_exec_query_stats to find cached queries with the highest total and average CPU usage. I would use Query Store to identify historical CPU spikes and plan regressions. Finally, I would inspect execution plans for scans, high logical reads, incorrect estimates, expensive operators and excessive parallelism, then apply and validate the appropriate fix.

Key Points

Check both total CPU and average CPU.
Frequently executed small queries can cause high CPU.
Query Store is valuable for historical analysis.
High logical reads often lead to high CPU.
Do not restart SQL Server or clear the plan cache before collecting evidence.

Find SQL Queries Causing High CPU