CPU Scheduler Waiting on Disk: Read Pending Disk IO

A CPU scheduler waiting on disk shows up as pending disk IO. The count comes from sys.dm_os_schedulers, and one short query is enough to read it.

Which Resource Is Slow

During a performance health check, I told a client the disk was the first thing to check. A claim like that needs a number behind it, and SQL Server keeps one on every scheduler. A scheduler is the part of SQL Server that gives a CPU its work. Each scheduler counts the IO requests that were sent to the disk and have not come back. A CPU scheduler waiting on disk has a pending count above zero.

Three columns of sys.dm_os_schedulers matter here. The column pending_disk_io_count holds the IO requests that are waiting to complete. The column work_queue_count holds the tasks that wait for a worker. The column runnable_tasks_count holds the tasks that have a worker and wait for the CPU. The last one is the CPU pressure signal. The pending IO column is the disk signal.

Read It Once

The first query adds the three columns over all schedulers that run user work. Schedulers with an id of 255 or more are internal, so the filter leaves them out. The query also shows the highest pending count on a single scheduler, because a total hides a lopsided load. The query needs VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. The CPU side of these columns has its own post, linked below.

SELECT COUNT(*) AS Schedulers,
       SUM(pending_disk_io_count) AS PendingTotal,
       MAX(pending_disk_io_count) AS PendingMax,
       SUM(work_queue_count) AS WorkQueue,
       SUM(runnable_tasks_count) AS Runnable
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255 AND status = N'VISIBLE ONLINE';

This is one reading from a quiet development server with 16 schedulers, so every number is zero. A quiet server is a normal result. The view shows the state at this moment, and it needs a busy moment to show anything.

Sample It Over Time

A single reading is a photograph. Pending IO comes and goes in fractions of a second, so a longer look is more useful. The script below takes 30 readings, half a second apart, and keeps them in a temporary table. The loop ends after 30 rounds, so it cannot run forever.

SET NOCOUNT ON;
DROP TABLE IF EXISTS #Samples;
CREATE TABLE #Samples (SampleNo int, PendingTotal int, PendingMax int, WorkQueue int, Runnable int);
DECLARE @i int = 1;
WHILE @i <= 30
BEGIN
    INSERT #Samples
    SELECT @i, SUM(pending_disk_io_count), MAX(pending_disk_io_count), SUM(work_queue_count), SUM(runnable_tasks_count)
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255 AND status = N'VISIBLE ONLINE';
    WAITFOR DELAY '00:00:00.500';
    SET @i += 1;
END;
SELECT COUNT(*) AS Samples, MAX(PendingTotal) AS MaxPendingTotal, MAX(PendingMax) AS MaxOnOneScheduler,
       CAST(AVG(PendingTotal * 1.0) AS decimal(9,2)) AS AvgPending, MAX(WorkQueue) AS MaxWorkQueue, MAX(Runnable) AS MaxRunnable
FROM #Samples;
DROP TABLE #Samples;

The quiet server returns zeros again. To see the counters move, start the sampler first in one window. Then run a disk heavy load in another. The load below creates a database named DiskWaitDemo and writes 100,000 rows of 2,000 bytes each. It loads them with GENERATE_SERIES, which needs SQL Server 2022 and compatibility level 160. It then runs a CHECKPOINT, which writes the dirty pages to disk.

IF DB_ID(N'DiskWaitDemo') IS NULL CREATE DATABASE DiskWaitDemo;
GO
ALTER DATABASE DiskWaitDemo SET RECOVERY SIMPLE;
GO
USE DiskWaitDemo;
GO
DROP TABLE IF EXISTS dbo.Big;
CREATE TABLE dbo.Big (ID int NOT NULL PRIMARY KEY, Pad char(2000) NOT NULL);
INSERT INTO dbo.Big SELECT value, 'x' FROM GENERATE_SERIES(1, 100000);
CHECKPOINT;

The sampler ran in the other window during the load. In two runs the highest total was 1, and the average over 30 samples was 0.03. The load needed at most one waiting request on the test server.

Your result will differ. On a slow or shared disk, the same load would show a higher count for longer. Read the pattern across samples, not one value.

Confirm With File Latency

Pending IO says that requests wait. It does not say which file is slow. IO Stalls by Database File: Find the File to Move covers file latency in full. The view sys.dm_io_virtual_file_stats counts the stall time of each file since the instance started. The query divides it by the number of reads and writes, so you get the average milliseconds per operation. The three files with the most stall time come first.

SELECT TOP (3) DB_NAME(vfs.database_id) AS DatabaseName, mf.name AS FileName,
       CAST(vfs.io_stall_read_ms * 1.0 / NULLIF(vfs.num_of_reads, 0) AS decimal(9,2)) AS AvgReadMs,
       CAST(vfs.io_stall_write_ms * 1.0 / NULLIF(vfs.num_of_writes, 0) AS decimal(9,2)) AS AvgWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY vfs.io_stall DESC;

The result names a database and a file, so you know where to look next. The values are averages since the last restart, and they hide a short bad hour. Compare them with the pending count at the time of the complaint.

How to Read the Result

A CPU scheduler waiting on disk looks like CPU pressure, so read pending IO and the runnable count together. A high pending count with a low runnable count is a sign of the disk. A high runnable count with a low pending count is a sign of the CPU. Measure CPU Pressure in SQL Server with Waits and Schedulers shows how to read it. When both are high, the work is slow for two reasons at once. A work queue above zero means tasks wait for a worker.

Then investigate each scheduler on its own. A sum can hide one scheduler that carries all the pending requests. The MAX column in the query exists for that reason.

Is a Zero Good News?

You could argue that a zero proves the disk is fine. It proves that nothing was waiting at the moment you looked. A slow disk shows up in the file latency and in waits such as PAGEIOLATCH and WRITELOG. Use the pending count as a quick first test, and keep the latency query for the second.

What to Remember

A CPU scheduler waiting on disk is a pending disk IO count above zero, kept on each scheduler. Read it once, then sample it. A sustained value above zero deserves a look at file latency. Compare the runnable count to decide between disk and CPU.

When you finish, drop the demo database.

USE master;
GO
ALTER DATABASE DiskWaitDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE DiskWaitDemo;

A busy CPU is not always a CPU problem, it is sometimes a disk the processor is waiting on.

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