Measure CPU Pressure in SQL Server with Waits and Schedulers

CPU pressure is a queue: tasks are ready to run, but every scheduler is busy, so they wait. SQL Server counts that queue for you, and a few short queries show how long it is.

What CPU Pressure Means

SQL Server runs a small scheduler of its own on top of Windows. It creates one scheduler for each logical processor, and a scheduler runs one task at a time. A task is a unit of work, such as one piece of a query.

A task is always in one of three states: running, runnable or suspended. Running means it holds the scheduler. Runnable means it is ready and waits for its turn. Suspended means it waits for something else, such as a disk read or a lock. Only the runnable state points at the processor, and a runnable queue that keeps growing is CPU pressure.

High CPU use alone doesn’t prove pressure. A busy processor with an empty queue is working well. The percentage tells you how busy the processor is, and the queue tells you whether anyone is waiting. In my health checks, I read the queue first and the percentage second.

Read the Scheduler Queue First

The first query sums the queue across all online schedulers. runnable_tasks_count is the number of tasks waiting for their turn. work_queue_count is different: it counts tasks that haven’t received a worker thread yet. pending_disk_io_count counts disk requests still waiting, which points at storage, not the processor.

SELECT COUNT(*) AS OnlineSchedulers,
       SUM(runnable_tasks_count) AS RunnableTotal,
       MAX(runnable_tasks_count) AS RunnableWorst,
       SUM(work_queue_count) AS WaitingForWorker,
       SUM(pending_disk_io_count) AS PendingIO
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE';

The filter on status keeps out the hidden schedulers that SQL Server uses for itself. My test instance is idle, so every count is zero. A busy server shows a runnable total above zero. One reading is a photograph. Take several a few seconds apart, and look for a queue that keeps showing up.

The second query lists the three schedulers with the longest queues. It shows whether the queue is spread across all schedulers or piled onto one.

SELECT TOP (3) scheduler_id, cpu_id, current_tasks_count, runnable_tasks_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY runnable_tasks_count DESC, scheduler_id;

current_tasks_count is not a queue. It includes suspended tasks, so a high number there says little.

Compare Signal Waits With Resource Waits

Every wait in SQL Server has two parts. The resource wait is the time spent waiting for the thing itself, such as a lock. The signal wait is the time spent afterward, waiting for a scheduler to become free. Signal time is CPU queue time by definition. The wait_time_ms column already includes it, so resource wait is the difference between the two columns.

The counters in sys.dm_os_wait_stats are cumulative since the server started, so an old spike can hide a quiet present. The script below takes a reading, waits ten seconds and measures only what happened in between. It skips idle background waits with a short pattern list. If your server shows another idle wait at the top, add its pattern.

DECLARE @before TABLE (wait_type nvarchar(60) PRIMARY KEY, wait_ms bigint, signal_ms bigint, tasks bigint);
INSERT INTO @before (wait_type, wait_ms, signal_ms, tasks)
SELECT wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats;
WAITFOR DELAY '00:00:10';
SELECT SUM(a.wait_time_ms - b.wait_ms) AS WaitMs,
       SUM(a.signal_wait_time_ms - b.signal_ms) AS SignalMs,
       CAST(100.0 * SUM(a.signal_wait_time_ms - b.signal_ms)
            / NULLIF(SUM(a.wait_time_ms - b.wait_ms), 0) AS decimal(5,1)) AS SignalPercent
FROM sys.dm_os_wait_stats AS a
JOIN @before AS b ON b.wait_type = a.wait_type
WHERE NOT EXISTS (SELECT 1
                  FROM (VALUES (N'%SLEEP%'), (N'%QUEUE%'), (N'%TIMER%'), (N'%BROKER%'), (N'XE%'),
                               (N'HADR%'), (N'SQLTRACE%'), (N'WAITFOR'), (N'DIRTY_PAGE_POLL'), (N'CLR%')) AS idle(pattern)
                  WHERE a.wait_type LIKE idle.pattern);
SELECT a.wait_type,
       a.waiting_tasks_count - b.tasks AS NewWaits,
       a.wait_time_ms - b.wait_ms AS WaitMs,
       a.signal_wait_time_ms - b.signal_ms AS SignalMs
FROM sys.dm_os_wait_stats AS a
JOIN @before AS b ON b.wait_type = a.wait_type
WHERE a.wait_type IN (N'SOS_SCHEDULER_YIELD', N'THREADPOOL');

On an idle test instance, the signal share is a few percent. A quiet server mostly measures background tasks, so a low value isn’t proof of health. A commonly quoted rule of thumb says to look closer when it stays above 20 to 25 percent. Treat that as a prompt, not a limit. I also compare the share with the same server on a normal day.

Two waits in the second result need a name. SOS_SCHEDULER_YIELD means a task gave up the scheduler so another task could run, and it joins the runnable queue again. Many of them, with high signal time, is a busy-processor pattern. THREADPOOL means a task found no free worker thread. That’s a different shortage. The documentation ties it to a worker limit set too low, or to unusually long batches.

Check the Recent CPU History

SQL Server also records its own CPU use about once a minute, in an in-memory log called a ring buffer. This query reads the last five samples. SqlServerPercent is the share SQL Server used. IdlePercent is the share nobody used. OtherProcessesPercent is what’s left for everything else on the machine.

DECLARE @now bigint = (SELECT ms_ticks FROM sys.dm_os_sys_info);
SELECT TOP (5)
       DATEADD(SECOND, -CAST((@now - rb.[timestamp]) / 1000 AS int), SYSDATETIME()) AS SampleTime,
       rb.rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SqlServerPercent,
       rb.rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS IdlePercent,
       100 - rb.rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int')
           - rb.rec.value('(/Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS OtherProcessesPercent
FROM (SELECT [timestamp], CAST(record AS xml) AS rec
      FROM sys.dm_os_ring_buffers
      WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR') AS rb
ORDER BY rb.[timestamp] DESC;

On a server meant for SQL Server alone, a large last column means another program takes processor time. Tuning queries won’t give that time back. On my desktop test machine the value sits near 20 because other programs run beside SQL Server.

Find the Queries That Burn the CPU

When the queue is real, find who builds it. sys.dm_exec_query_stats keeps running totals for every statement still in the plan cache. total_worker_time is CPU time in microseconds, so the query divides by 1,000 to show milliseconds. Sorting by the total finds the statements that cost the server the most.

SELECT TOP (5)
       qs.execution_count AS Runs,
       qs.total_worker_time / 1000 AS TotalCpuMs,
       qs.total_worker_time / qs.execution_count / 1000.0 AS AvgCpuMs,
       CAST(100.0 * qs.total_worker_time / NULLIF(qs.total_elapsed_time, 0) AS decimal(6,1)) AS CpuPercentOfElapsed,
       LEFT(REPLACE(REPLACE(SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
            (CASE WHEN qs.statement_end_offset = -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END
             - qs.statement_start_offset) / 2 + 1), CHAR(13), N' '), CHAR(10), N' '), 60) AS StatementStart
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;

On an idle instance, the top statements are the monitoring scripts themselves. CpuPercentOfElapsed compares CPU time with elapsed time. A value near 100 means the statement spent its life on the processor. A low value means it spent most of its time waiting for something else, so the processor isn’t its problem.

Parallel plans can show more than 100, because several threads add up their CPU time. The plan cache also forgets statements after a restart or a recompile. A short list on a freshly restarted server tells you little.

Faster CPU, More Cores or Better Queries

The answer depends on the shape of the load. If one heavy query runs alone on one busy scheduler, a faster clock helps. If many sessions fill every scheduler and the queue is long, more cores help. If a handful of statements account for most of the CPU time, fix those first.

I put query fixes first for a practical reason. A missing index can make a statement read a million rows to return ten. Extra cores only let the server waste that effort faster. Core-based licensing charges per core, so every core you add also adds to the bill.

You could argue that adding cores is the faster fix, and during an outage it can be. On a virtual machine it takes a few clicks. The catch: the same bad query runs on the bigger machine. The queue returns as the load grows.

These queries measure pressure, not speed. To compare processors across servers, run the same repeatable workload on each. Compare total CPU time and the signal wait share, not the wall clock alone.

What to Remember

Read in this order: the scheduler queue, the signal wait share, the recent CPU history, and then the top statements. The first two tell you whether pressure exists. The third tells you whether SQL Server or something else uses the processors. The last tells you which statements to fix.

Take readings during the slow period, not afterward, and keep a baseline from a normal day. All five scripts only read system views and change nothing on the server. They show current state, so run them again after every change you make. The views need the VIEW SERVER STATE permission, named VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

CPU pressure is not a high percentage, it is a queue of tasks waiting for a turn.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.