SQL CPU Schedulers

Overview

The SQL CPU Schedulers report answers is this SQL Server short of CPU – drawn as a core wall. One tile per scheduler, grouped into NUMA node blocks, coloured by how many tasks are queued waiting for that CPU, and re-read every few seconds.

One tile per scheduler matters because the core count and its arrangement are half the finding. A chart that aggregates scheduler load draws the same picture for a four core VM as for a ninety six core server. Here, thirty two schedulers look like thirty two tiles, and a node running hot beside a node sitting idle is visible before you read a single label.

The report samples rather than snapshots, because runnable_tasks_count is an instantaneous reading. One sample of zero does not mean the instance is healthy and one sample of four does not mean it is sick. The wall moves, each tile carries a sparkline of its own recent history, and the second shape plots the whole instance against time.

This replaces the earlier eight column grid of the same name, which could not have a chart at all – it was a type="Grid" report definition – and which read eight of the twenty or so columns sys.dm_os_schedulers offers, leaving out the NUMA node, the online flag, the load factor, the pending I/O count, and the worker creation failure flag.

Where to find it

Route How
Server tree Right-click the server → Instance Level Reports → SQL CPU Schedulers
Related links From CPU Load, CPU by Database, Waits or What is Active
Report arrows Sits between Sessions and Structure Change Log

The page title reads CPU Schedulers for <server name>.

The SQL CPU Schedulers report
The core wall above the per scheduler grid, with the node blocks and the header rail.

Requirements

  • Any supported version. sys.dm_os_schedulers and sys.dm_os_sys_info both go back to SQL Server 2005, and there is no version gate on the page.
  • Scheduler Delay and CPU Used need SQL Server 2016 or newer. They come from total_scheduler_delay_ms and total_cpu_usage_ms. On earlier versions the two columns are left off the grid entirely rather than shown against blanks, and the subtitle line says so.
  • Affinity and soft-NUMA are best effort. They are read once, on a query of their own, because a column that does not exist fails a whole batch rather than just its own statement – and Azure SQL Database does not offer all of them. If they cannot be read the chips are simply not drawn, and nothing else on the page is affected.
  • VIEW SERVER STATE on the instance.
  • The query timeout is 30 seconds, deliberately short: a sample that has not come back by the time the next one is due is better abandoned than queued behind.

What a scheduler is

SQL Server does not hand work to the operating system scheduler. It runs its own, one scheduler per logical CPU, and each scheduler hands its CPU to one worker at a time.

Term What it means
Task A unit of work attached to the scheduler – running, waiting, or asleep.
Runnable Ready to execute and waiting only for the CPU. This is the pressure number.
Worker A thread. Workers are created on demand up to max_workers_count.
Work queue Tasks with no worker available to run them. Non-zero here is serious.
Load factor SQL Server’s own measure of how loaded a scheduler is, used to place new tasks.

A task that is runnable has everything it needs except a CPU. A long runnable queue is the definition of CPU pressure, and it is what this page colours.

Reading the core wall

The core wall
One tile per scheduler, grouped by NUMA node. Colour is the runnable queue and nothing else.

The tiles

Each tile is one scheduler. Colour comes from the runnable queue and nothing else, so a tile means the same thing on every node, on every instance, at every sample:

Fill Meaning
Slate Nothing waiting for the CPU.
Amber One task waiting for the CPU.
Deep amber Two waiting.
Red Three or more waiting.
Dashed outline Visible offline – this instance is not using that CPU.

Colour is the queue and only the queue. There is no “this CPU is busy” colour, because sys.dm_os_schedulers cannot honestly supply one: an idle SQL Server carries three to six tasks on every scheduler – background tasks and the workers of sleeping sessions – so any task-count threshold paints an idle box as a busy one. How much work a scheduler is carrying is a real question, and it is answered by the load bar on each tile, scaled against the busiest scheduler on the instance.

The practical effect is that a calm wall means a calm server, and any colour above slate means somebody is waiting for a CPU.

Three things deliberately do not rest on colour alone:

  • The queue depth is printed on the tile whenever it is above zero, so the finding survives a greyscale printout and a colour blind reader.
  • Offline is a texture, a dashed outline rather than a fill, because an offline scheduler is a configuration fact rather than a problem.
  • The busiest scheduler carries an inset ring, so it can be picked out of a row of tiles that share a colour.

A corner dot marks pending disk I/O. The bar across the bottom of each tile is the total task count, scaled against the busiest scheduler on the instance. The line across the middle is that scheduler’s own runnable queue over the last twenty samples.

Tiles shrink as the core count climbs – they lose the sparkline, then the text, then the bar, then their size. Nothing is ever dropped; a ninety six core box simply draws smaller tiles than a four core one, and the smaller tiles are themselves the signal.

The node blocks

Schedulers are grouped by parent_node_id. Each block heading carries the node number, how many of its schedulers are online and offline, how many tasks are waiting, and its worker count.

An instance with a single NUMA node gets no heading at all – a label reading NUMA node 0 and nothing else is furniture.

Internal schedulers – the dedicated admin connection and the resource monitor, which are scheduler_id 255 and up – are hidden by default and get a block of their own when the toolbar asks for them. They are not CPUs the workload runs on.

The header

The summary line answers the question before the eye reaches a tile:

24 schedulers online · 2 offline · 7 tasks waiting for CPU on 4 schedulers

The chip strip carries the facts that belong to the box rather than to any one scheduler:

  • 32 logical CPUs
  • workers 412 / 704 – turns red past three quarters of max_workers_count
  • signal wait 4.1% – turns amber at 20%
  • affinity manual – drawn only when affinity is not automatic, because it is usually the reason schedulers are offline
  • 2 internal hidden – so hiding them is visibly a choice

The notice band appears when something needs saying, worst finding first:

  1. Worker creation has failed on one or more schedulers since startup. This is worker thread exhaustion and it outranks everything else on the page.
  2. Worker threads are past three quarters of the maximum. At the limit, new connections wait for a worker, which is THREADPOOL and an instance nobody can log in to.
  3. Tasks are waiting for CPU across several schedulers.
  4. Schedulers are visible but offline – an affinity mask or an edition core limit rather than a fault. Only shown when nothing worse is.

The two shapes

  • Cores (the default) – the wall described above.
  • Pressure – the same samples re-plotted against time as a stacked area, one band per NUMA node, running from the start of the sample window to now.

Pressure is the shape that pays for the sampling. A core wall answers where is the queue. It cannot answer has it been there for a while, and on a counter that changes several times a second that is usually the question that matters. A plateau is the finding; a single tall spike almost never is.

The axis says how far back the window actually reaches, so a page opened twenty seconds ago says 20s ago rather than claiming ten minutes it never saw.

Chart interactions

Gesture Result
Hover a tile Scheduler tooltip
Click a tile Selects that scheduler in the grid
Double-click a tile Opens what is running on this scheduler
Right-click a tile The scheduler menu, plus Copy Chart to Clipboard
Click a node heading Filters the grid to that node
Right-click a node heading The node menu
Right-click empty space Copy Chart to Clipboard only

Reading the grid

The scheduler grid
Every scheduler, with its runnable queue, load and worker counts.

Every scheduler is in the grid, whatever the chart is showing.

Column What it is
Scheduler scheduler_id. 255 and up are internal.
CPU cpu_id, the logical CPU it is bound to.
Node parent_node_id, the NUMA node.
Status Visible online, visible offline, hidden online, and so on, with a dot in the tile’s own colour.
Tasks Everything attached to the scheduler.
Runnable Tasks waiting only for the CPU, drawn as a pill in the tile’s colour.
Workers Threads currently associated with it.
Active Workers actually doing something.
Work Queue Tasks with no worker to run them.
Pending I/O Outstanding disk I/O. Blank when zero.
Load Factor SQL Server’s own load measure.
Scheduler Delay total_scheduler_delay_ms, cumulative since startup. 2016+.
CPU Used total_cpu_usage_ms, cumulative since startup. 2016+.

The Runnable pill replaced a colour tag that the old report wrote into the result set as the literal strings [YELLOW]: and [RED]: and then parsed back out. The finding is the same; it is now decided from the number, which means it can also colour the tile and the chip from the same place, and the column sorts as a number.

The delay and CPU columns keep raw milliseconds behind the displayed text so the header sort is a real sort – a list view sorts as text, and would read 2.4 m as 2.4.

The toolbar

Group Buttons
Shape Cores · Pressure
Hidden schedulers (toggle)
Refresh · Pause

A countdown sits to the right. The page samples itself every 5 seconds by default – schedulers move a great deal faster than sessions do – on its own private timer rather than through the application’s shared navigation timer, which would push every firing into the go-back history and the popular-reports tracking.

Pause stops the countdown and survives navigating away and coming back. Sampling is also held automatically while a right-click menu is open, so the rows cannot move out from under a menu you are reading.

About the sample cost

sys.dm_os_schedulers is a memory read. There are no locks, no I/O, and the row count is the core count, so this is about as cheap as a query gets. It is still a poll, which is why the interval is configurable and Pause is one click.

The sample buffer holds 120 readings – ten minutes at the default interval. It is thrown away when you leave the page. Nothing here is written to the Historic database.

What is running on this scheduler

Double-click a tile, or right-click and choose Show what is running on scheduler N, and a pane opens on the right listing what sys.dm_exec_requests says is on that scheduler right now.

Column What it is
Session The session id.
State running, runnable, suspended, or blocked.
Elapsed How long the request has been going.
Waiting on The wait type – or the CPU for a runnable request, or the blocking session.

Selecting a row shows its statement in a monospace box underneath, with the login, database, command, CPU and elapsed time as a comment header.

A runnable request beside SOS_SCHEDULER_YIELD is the confirmation that what you are looking at is genuinely CPU pressure rather than something else queueing behind it.

The request list is re-read on every sample while the pane is open, and it is a separate query – joining sys.dm_exec_sql_text into the five second poll would put plan cache lookups on the heartbeat of the page.

Nothing in the pane acts on the request. A report about CPU pressure is not the place to be one click away from killing somebody’s query.

How to read the report

  1. Read the summary line and the notice band. If worker threads are named, start there – that is the finding that ends in an outage rather than a slowdown.
  2. Look at the colour mix. A wall of slate is a server keeping up, however busy it is. Amber and red are queues. Read the load bars to see how much work is behind that.
  3. Compare the nodes. One node hot and another idle is a NUMA or affinity problem, not a CPU shortage.
  4. Switch to Pressure. A plateau is real. A spike is noise.
  5. Check the signal wait chip. Above 20% across the whole instance corroborates what the tiles are showing.
  6. Double-click the reddest tile and look for SOS_SCHEDULER_YIELD.
  7. Cross-check on Waits – SOS_SCHEDULER_YIELD high in the wait statistics is the same finding measured cumulatively.

Common patterns

All slate, but the load bars are full. The instance is busy and keeping up. This is what a healthy loaded server looks like, and it is not a problem to solve. The bars say there is work; the absence of colour says nothing is queueing behind it.

One or two red tiles, the rest quiet. Usually a single parallel query, or a task pinned to a scheduler. Double-click it.

Every tile amber or red, on every node. Genuine CPU shortage. More cores, less parallelism, or better queries.

One node red, the other slate. Affinity, soft-NUMA, or a workload landing on one node. Check the affinity chip and the offline count before assuming you need more CPU.

Half the tiles dashed. An edition core limit or an affinity mask. Standard Edition caps the cores SQL Server will use, and the schedulers for the rest are created and left offline. This surprises people who have just bought a bigger server.

Worker count climbing towards the maximum. Look for long blocking chains first. Blocked sessions hold their workers, so blocking is the usual road to THREADPOOL.

Work Queue above zero. Tasks exist with no thread to run them. This is the state immediately before an instance stops accepting connections.

Where the data comes from

Live, every sample, from sys.dm_os_schedulers, with sys.dm_os_sys_info for the CPU count and the worker maximum and sys.dm_os_wait_stats for the signal wait ratio. sys.dm_exec_requests is read only while the detail pane is open. Nothing is stored.

Signal wait, Scheduler Delay and CPU Used are cumulative since startup, not since the page opened. They describe the instance’s whole life, which makes them good for corroboration and poor for spotting something that started ten minutes ago. That is what the Pressure shape is for.

Settings

Setting Default Values
CpuSchedulersShape Cores Cores, Pressure
CpuSchedulersShowHidden off on / off
CpuSchedulersRefreshInterval 5 seconds, minimum 2
CpuSchedulersAutoRefreshPaused off on / off

Messages you may see

No rows:

sys.dm_os_schedulers returned no rows for this instance. That should not happen on a running SQL Server. Check that the login has VIEW SERVER STATE, and try the Refresh button.

Timed out:

The scheduler lookup did not finish in time. Try the Refresh button, or check the status of the SQL Server.

Still collecting, on the Pressure shape in the first few seconds:

Collecting samples. The first few seconds of a page have nothing to plot yet.

Report Why you would go there
Waits SOS_SCHEDULER_YIELD and THREADPOOL, measured cumulatively.
CPU Load by Hour by Day Whether this is the busy hour or an unusual one.
CPU by Database Which database is spending the CPU.
What is Active Everything running, not just what is on one scheduler.

Frequently asked questions

Why are some schedulers offline? An affinity mask, or an edition core limit. SQL Server creates a scheduler per logical CPU and leaves the ones it is not licensed or configured to use in VISIBLE OFFLINE. It is not a fault, and the notice band says so when nothing worse is happening.

What is a good runnable count? Zero, most of the time. Brief ones are normal on a busy server. What matters is whether the queue persists, which is exactly what the Pressure shape shows.

Why does the number change every time I look? Because it is an instantaneous reading of a counter that moves several times a second. That is the whole reason this page samples and keeps a window instead of showing one number in a table.

Why are internal schedulers hidden? scheduler_id 255 and up are the dedicated admin connection and the resource monitor. They are not CPUs your workload runs on, and including them makes the core count wrong. The toolbar button shows them in a block of their own.

Why is Scheduler Delay so large? It is cumulative since the scheduler came online, so on a server that has been up for months it will be. Compare schedulers against each other rather than reading the absolute number.

Why is there no Scheduler Delay column on my server? It needs SQL Server 2016 or newer. The column is left off rather than shown empty, and the subtitle line says why.

Does this report affect the server? It reads a memory-resident DMV every five seconds with no locks and no I/O. If you would rather it did not, the Pause button stops it and the interval is configurable.