sys.dm_os_schedulers - Usage & Examples

SQL Server's Scheduler Manager creates one scheduler per logical CPU (bounded by any configured affinity mask), regardless of MAXDOP. MAXDOP governs the maximum degree of parallelism for an individual query, not the total number of schedulers the instance creates. Each scheduler operates as a cooperative multitasking environment using the SQLOS layer, managing a queue of runnable tasks and a pool of worker threads. The scheduler's internal state machine transitions workers between RUNNING, RUNNABLE, SUSPENDED, and DONE states based on resource availability and task completion.

The runnable_tasks_count metric reveals tasks waiting for CPU cycles. Values consistently above 0 indicate CPU starvation, where more tasks compete for execution than the scheduler can immediately service. The active_workers_count shows currently executing threads, while pending_disk_io_count exposes I/O bound operations that have yielded the scheduler.

SQL Server 2016 introduced automatic soft-NUMA partitioning, affecting scheduler topology on systems with more than 8 logical processors. SQL Server 2019 enhanced scheduler visibility for columnstore and memory-optimized workloads. SQL Server 2022 added improved scheduler metrics for intelligent query processing operations.

The scheduler's cooperative model differs fundamentally from Windows preemptive scheduling. Tasks voluntarily yield control at specific yield points (I/O operations, locks, latches), preventing single queries from monopolizing CPU resources. Scheduler starvation occurs when tasks fail to yield appropriately or when excessive context switching overwhelms the cooperative model.

-- Current scheduler health overview
SELECT 
    scheduler_id,
    cpu_id,
    status,
    is_online,
    current_tasks_count,
    runnable_tasks_count,
    current_workers_count,
    active_workers_count,
    work_queue_count,
    pending_disk_io_count,
    total_cpu_usage_ms,
    total_scheduler_delay_ms
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
ORDER BY runnable_tasks_count DESC;

-- Identify CPU pressure and scheduler imbalance
SELECT 
    scheduler_id,
    runnable_tasks_count,
    active_workers_count,
    work_queue_count,
    CASE 
        WHEN runnable_tasks_count > 0 THEN 'CPU Pressure'
        WHEN work_queue_count > 10 THEN 'Queue Buildup'
        ELSE 'Normal'
    END AS scheduler_status,
    total_cpu_usage_ms / NULLIF(total_scheduler_delay_ms, 0) AS cpu_efficiency_ratio
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
    AND status = 'VISIBLE ONLINE';

-- Historical scheduler performance trending
SELECT 
    s.scheduler_id,
    s.total_cpu_usage_ms,
    s.total_scheduler_delay_ms,
    CAST(s.total_cpu_usage_ms AS DECIMAL(20,2)) / 
        NULLIF(s.total_cpu_usage_ms + s.total_scheduler_delay_ms, 0) * 100 AS cpu_utilization_pct,
    s.yield_count,
    s.preemptive_switches_count,
    s.context_switches_count
FROM sys.dm_os_schedulers s
WHERE s.scheduler_id < 255
    AND s.is_online = 1
ORDER BY cpu_utilization_pct DESC;

-- Detect scheduler anomalies and bottlenecks
SELECT 
    scheduler_id,
    current_tasks_count,
    runnable_tasks_count,
    pending_disk_io_count,
    CASE 
        WHEN runnable_tasks_count > 5 THEN 'High CPU Queue'
        WHEN pending_disk_io_count > 20 THEN 'I/O Bound'
        WHEN active_workers_count = 0 AND current_tasks_count > 0 THEN 'Worker Starvation'
        ELSE 'Healthy'
    END AS bottleneck_type,
    total_cpu_usage_ms - LAG(total_cpu_usage_ms) OVER (PARTITION BY scheduler_id ORDER BY scheduler_id) AS cpu_delta_ms
FROM sys.dm_os_schedulers
WHERE scheduler_id < 255
    AND status = 'VISIBLE ONLINE';

-- Worker thread utilization analysis
SELECT 
    s.scheduler_id,
    s.active_workers_count,
    s.current_workers_count,
    w.max_workers_count,
    CAST(s.active_workers_count AS DECIMAL(10,2)) / NULLIF(w.max_workers_count, 0) * 100 AS worker_utilization_pct,
    s.work_queue_count
FROM sys.dm_os_schedulers s
CROSS JOIN sys.dm_os_sys_info w
WHERE s.scheduler_id < 255
    AND s.is_online = 1
    AND s.active_workers_count > 0
ORDER BY worker_utilization_pct DESC;

Immediate CPU Pressure Relief This script identifies and kills the highest CPU consuming sessions when runnable_tasks_count indicates severe CPU starvation.

-- WARNING: Test in dev first. This kills active sessions.
DECLARE @high_cpu_spid INT;
DECLARE @kill_command NVARCHAR(100);

-- Only execute if severe CPU pressure detected
IF EXISTS (
    SELECT 1 FROM sys.dm_os_schedulers 
    WHERE scheduler_id < 255 AND runnable_tasks_count > 10
)
BEGIN
    SELECT TOP 1 @high_cpu_spid = r.session_id
    FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
    WHERE s.is_user_process = 1
        AND r.cpu_time > 10000 -- More than 10 seconds CPU
        AND r.status = 'running'
    ORDER BY r.cpu_time DESC;

    IF @high_cpu_spid IS NOT NULL
    BEGIN
        SET @kill_command = 'KILL ' + CAST(@high_cpu_spid AS NVARCHAR(10));
        PRINT 'Executing: ' + @kill_command;
        EXEC sp_executesql @kill_command;
    END
END

MAXDOP Optimization Based on Scheduler Health Dynamically adjusts MAXDOP when scheduler imbalance is detected across NUMA nodes.

-- Analyzes scheduler distribution and recommends MAXDOP adjustment
WITH scheduler_stats AS (
    SELECT 
        cpu_id / 8 AS numa_node, -- Approximate NUMA grouping
        COUNT(*) AS scheduler_count,
        AVG(CAST(runnable_tasks_count AS DECIMAL(10,2))) AS avg_runnable_tasks,
        MAX(runnable_tasks_count) AS max_runnable_tasks
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255 AND is_online = 1
    GROUP BY cpu_id / 8
),
recommended_maxdop AS (
    SELECT 
        CASE 
            WHEN MAX(max_runnable_tasks) > 5 THEN scheduler_count / 2
            WHEN AVG(avg_runnable_tasks) > 2 THEN scheduler_count * 3 / 4
            ELSE scheduler_count
        END AS new_maxdop
    FROM scheduler_stats
)
SELECT 
    'EXEC sp_configure ''max degree of parallelism'', ' + CAST(new_maxdop AS VARCHAR(10)) + '; RECONFIGURE;' AS recommended_command
FROM recommended_maxdop;

-- Execute the command manually after review

Worker Thread Pool Adjustment Increases max worker threads when scheduler analysis shows consistent worker starvation.

-- Calculates optimal worker thread configuration
DECLARE @current_workers INT, @recommended_workers INT;

SELECT @current_workers = max_workers_count FROM sys.dm_os_sys_info;

WITH worker_analysis AS (
    SELECT 
        AVG(CAST(active_workers_count AS DECIMAL(10,2))) AS avg_active_workers,
        MAX(active_workers_count) AS peak_active_workers,
        COUNT(*) AS online_schedulers
    FROM sys.dm_os_schedulers
    WHERE scheduler_id < 255 AND is_online = 1
)
SELECT 
    @recommended_workers = CASE 
        WHEN peak_active_workers > @current_workers * 0.8 THEN @current_workers * 1.5
        WHEN avg_active_workers > @current_workers * 0.6 THEN @current_workers * 1.25
        ELSE @current_workers
    END
FROM worker_analysis;

IF @recommended_workers > @current_workers
BEGIN
    PRINT 'Current max workers: ' + CAST(@current_workers AS VARCHAR(10));
    PRINT 'Recommended: EXEC sp_configure ''max worker threads'', ' + CAST(@recommended_workers AS VARCHAR(10)) + '; RECONFIGURE;';
END

Monitor runnable_tasks_count consistently through automated alerts triggering when values exceed 2 for more than 30 seconds across multiple schedulers. Establish baseline measurements during off-peak hours to identify normal scheduler behavior patterns for your workload.

Configure MAXDOP appropriately for your NUMA topology, typically setting it to the number of physical cores per NUMA node rather than total logical processors. Use trace flag 8048 to enable NUMA-aware scheduler assignment in SQL Server versions prior to 2016's automatic soft-NUMA.

Implement connection pooling to prevent worker thread exhaustion. Set max worker threads to 0 (auto-configuration) unless specific workload testing proves manual tuning necessary. SQL Server 2019's intelligent query processing reduces scheduler pressure for many workloads through adaptive query plans.

Deploy query store and automatic plan correction to prevent plan regression that causes excessive CPU consumption. Enable Resource Governor for mixed workloads to prevent single applications from monopolizing scheduler resources. Regular index maintenance prevents excessive CPU usage from inefficient query plans that overwhelm the cooperative scheduling model.