SQL SERVER – CPU Scheduler Waiting On Disk

“It may be your disk which is slowing down your system, otherwise your CPU is all fine,” I said to one of my clients during Comprehensive Database Performance Health Check. Let us figure out today a very simple and effective script for CPU Scheduler Waiting On Disk.

It is quite easy to figure out how many CPU task counts are waiting on the Disk IO with the help of sys.dm_os_schedulers. I have derived this script from my previous article here: SQL SERVER – Measure CPU Pressure – Detect CPU Pressure and SQL SERVER – Query for CPU Pressure.

CPU Scheduler Waiting On Disk

SELECT	COUNT(*) Schedulers,
		AVG(work_queue_count) AS [Avg Work Queue Count],
		AVG(pending_disk_io_count) AS [Avg Pending DiskIO Count],
		SUM(work_queue_count) AS [SUM Work Queue Count],
		SUM(pending_disk_io_count) AS [SUM Pending DiskIO Count]
FROM sys.dm_os_schedulers WITH (NOLOCK)
WHERE scheduler_id < 255;

If you see the number of work queues and pending diskIO very high, it is clear to us that the CPU scheduler is waiting for either more CPU or more Disk IO. This is when we need to start investigating each scheduler individually for threads. If you want to learn more about this topic, you can learn by reading the blog post SQL SERVER – Troubleshooting High CPU.

If at any point in time, you are stuck or not able to move forward with your query tuning exercise, you can always reach out to me to help you. I usually answer very quickly to all the emails if I am not busy with the Comprehensive Database Performance Health Check. You can always reach me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)