SQL Server at 100% CPU: find the query, the cause and the fix

#Symptoms

• Host CPU is pinned at or near 100% and application timeouts climb.
• New connections are slow; SSMS takes seconds to open a query window.
• Waits show SOS_SCHEDULER_YIELD or a high share of signal wait time.
• Restarting the service "fixes" it for a few hours, then it comes back.

Do not restart first. A restart clears the plan cache and the DMVs you need to find the cause.

#1. Confirm SQL Server is the process using the CPU

From the host, compare total CPU with the SQL Server process. The process counter is summed across cores, so divide it by the logical processor count.

$cores = (Get-CimInstance Win32_ComputerSystem).NumberOfLogicalProcessors
$s = Get-Counter '\Processor(_Total)\% Processor Time','\Process(sqlservr)\% Processor Time' -SampleInterval 2 -MaxSamples 5
$s.CounterSamples | Select-Object Path, @{n='Pct';e={ if ($_.Path -like '*process(sqlservr)*') { [math]::Round($_.CookedValue / $cores,1) } else { [math]::Round($_.CookedValue,1) } }}

If you cannot log on to the host, the scheduler monitor ring buffer gives roughly the last few hours of SQL versus other-process CPU, one row per minute:

DECLARE @ts_now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info);
SELECT TOP (30)
       DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS sample_time,
       record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS sql_cpu_pct,
       100 - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]','int')
           - record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]','int') AS other_cpu_pct
FROM (SELECT [timestamp], CONVERT(xml, record) AS record
      FROM sys.dm_os_ring_buffers
      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
        AND record LIKE N'%<SystemHealth>%') AS rb
ORDER BY sample_time DESC;

If other_cpu_pct is high, the problem is outside SQL Server: antivirus scanning data files, a backup agent or another service. Fix that on the host instead.

#2. Check how much CPU pressure there is

-- Runnable tasks waiting for a CPU, per scheduler
SELECT scheduler_id, current_tasks_count, runnable_tasks_count, active_workers_count
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC;

-- Signal wait share since the last restart or clear
SELECT CAST(100.0 * SUM(signal_wait_time_ms) / NULLIF(SUM(wait_time_ms),0) AS decimal(5,2)) AS signal_wait_pct
FROM sys.dm_os_wait_stats;

Runnable tasks that stay above zero on most schedulers, together with a signal wait share above roughly 20-25%, mean queries are queuing for CPU.

#3. Find what is burning CPU right now

SELECT r.session_id, r.status, r.cpu_time, r.total_elapsed_time, r.logical_reads,
       r.dop, r.wait_type, s.login_name, s.host_name, s.program_name,
       DB_NAME(r.database_id) AS db,
       SUBSTRING(t.text, (r.statement_start_offset/2)+1,
         ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
           ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS statement_text,
       p.query_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
WHERE s.is_user_process = 1 AND r.session_id <> @@SPID
ORDER BY r.cpu_time DESC;

The dop column is available from SQL Server 2016. A few long requests with high cpu_time point at one bad plan. Hundreds of short requests point at a frequently called query or a compile storm.

#4. Find the top CPU queries over time

The plan cache shows aggregate cost for plans still in cache:

SELECT TOP (20)
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       qs.total_logical_reads / qs.execution_count AS avg_reads,
       qs.query_hash, qs.plan_handle,
       SUBSTRING(t.text, (qs.statement_start_offset/2)+1, 200) AS statement_start
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

If Query Store is on, it survives restarts and shows whether a query's plan changed. Run this in the affected database:

SELECT TOP (20) q.query_id, p.plan_id,
       SUM(rs.count_executions) AS execs,
       SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
       MAX(rs.last_execution_time) AS last_run,
       qt.query_sql_text
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE i.start_time >= DATEADD(hour, -2, SYSUTCDATETIME())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

The same query_id appearing with two plan_id values, one much more expensive, is a plan regression.

#5. Match the evidence to a cause

Check compile rate:

SELECT counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (N'Batch Requests/sec', N'SQL Compilations/sec', N'SQL Re-Compilations/sec');

These are cumulative values. Sample twice, ten seconds apart, and divide the difference by ten. Compilations above roughly 10% of batch requests deserve a look.

#6. Relieve the pressure

#Force a known good plan

EXEC sys.sp_query_store_force_plan @query_id = 4711, @plan_id = 902;
-- Later, once the root cause is fixed:
EXEC sys.sp_query_store_unforce_plan @query_id = 4711, @plan_id = 902;

#Evict one bad plan without clearing the whole cache

DBCC FREEPROCCACHE (0x06000500...);  -- plan_handle from step 3 or 4

Warning

DBCC FREEPROCCACHE with no argument clears every plan on the instance. Every query then compiles at once, which can make a CPU incident worse. Always pass a single plan_handle.

#Refresh statistics on the table behind the bad estimate

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

#Stop a runaway session

KILL 87;

Caution

Killing a session rolls back its open transaction. A long-running UPDATE or DELETE can take as long to roll back as it ran, holding locks the whole time. Check r.command and the transaction age in sys.dm_tran_active_transactions before you kill it, and agree it with the application owner.

#Prevent it next time

• Turn Query Store on for every user database and set capture mode to AUTO so regressions are visible and fixable after a restart.
• On SQL Server 2017 and later, consider automatic plan correction: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON);
• Alert on sustained CPU above 85-90% for 10 minutes and on runnable tasks per scheduler, not on single spikes.
• Keep statistics current with a scheduled job, and review the top CPU queries from step 4 weekly.
• Set MAXDOP and cost threshold for parallelism deliberately rather than leaving old defaults.
• Capture a CPU baseline per server so "high" has a number attached when the next incident starts.