Question: How much work is queued for each CPU in SQL Server? Look at visible online schedulers.
I hadn’t encountered this question in an interview. A client asked it during a Comprehensive Database Performance Health Check. CPU usage stayed high despite more than 56 active processors.
The client wanted to know where work was waiting. SQL Server’s scheduler DMV helps answer that narrower question. It doesn’t report a Windows CPU utilization percentage.
SELECT scheduler_id, cpu_id, parent_node_id,
current_tasks_count, runnable_tasks_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE status = N'VISIBLE ONLINE'
ORDER BY scheduler_id;The status filter selects schedulers available for ordinary user work. The older scheduler_id < 255 shortcut can omit schedulers on larger systems. Use cpu_id and parent_node_id to interpret the logical processor and node association.
| Column | What it tells you |
|---|---|
| current_tasks_count | Tasks associated with the scheduler, including tasks awaiting workers and tasks assigned to workers. |
| runnable_tasks_count | Workers with assigned tasks waiting for their turn on the runnable queue. |
| work_queue_count | Tasks waiting for a worker to pick them up. |
Take more than one sample
A single high count can disappear before your next query. Sample during the problem and look for a persistent pattern. Growing runnable queues suggest competition for scheduler time; pending work queues call for a worker-availability investigation.
I’d also compare waits, active requests and operating-system CPU measurements. More processors alone won’t fix an expensive query or blocked work. This DMV tells us where to investigate, not which setting to change.
SQL Server 2022 and later require VIEW SERVER PERFORMANCE STATE for this view. Earlier versions require VIEW SERVER STATE. Review access with your administrator rather than granting broad server privileges.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
