sys.dm_os_threads - Usage & Examples

SQL Server's thread management architecture operates through a hierarchical model where the SQLOS layer manages threads independently from Windows thread scheduling. Each SQL Server scheduler maps to a logical CPU and maintains its own thread pool. The dm_os_threads DMV exposes the raw OS threads that underpin this architecture, including worker threads, signal threads, lazy writer threads, checkpoint threads, and system threads.

sys.dm_os_threads exposes raw OS thread rows identified by os_thread_id, with thread_address and worker_address columns used to correlate to the SQLOS worker layer (sys.dm_os_workers) and, from there, to active tasks in sys.dm_os_tasks. Task states (RUNNING, RUNNABLE, SUSPENDED, etc.) live on sys.dm_os_tasks.task_state, not on sys.dm_os_threads. The kernel_time and usermode_time columns on sys.dm_os_threads accumulate CPU cycles spent in kernel mode (system calls, I/O operations) versus user mode (application code execution).

SQL Server 2016 introduced significant changes to thread management with the addition of batch mode operations and columnstore processing, which can spawn additional background threads. SQL Server 2019's adaptive query processing creates dynamic thread usage patterns that differ from earlier versions' more predictable threading models. SQL Server 2022's contained availability groups and parallel redo operations generate additional thread categories not present in previous versions.

Thread exhaustion occurs when SQL Server cannot create new worker threads due to reaching max worker threads limits or OS-level thread limits. This manifests as blocking on the THREADPOOL wait type and degraded query performance. Excessive context switching, visible through high kernel_time values relative to usermode_time, indicates scheduler pressure or poorly configured parallelism settings.

-- Identify threads with highest CPU consumption
SELECT TOP 20
    os_thread_id,
    thread_address,
    worker_address,
    kernel_time,
    usermode_time,
    (kernel_time + usermode_time) AS total_cpu_time,
    CASE 
        WHEN (kernel_time + usermode_time) > 0 
        THEN (kernel_time * 100.0) / (kernel_time + usermode_time) 
        ELSE 0 
    END AS kernel_time_pct
FROM sys.dm_os_threads
ORDER BY (kernel_time + usermode_time) DESC;

-- Analyze task state distribution by joining threads -> workers -> tasks
-- task_state lives on sys.dm_os_tasks, not sys.dm_os_threads
SELECT 
    tk.task_state,
    COUNT(*) AS thread_count,
    AVG(t.kernel_time) AS avg_kernel_time,
    AVG(t.usermode_time) AS avg_usermode_time,
    MAX(t.kernel_time + t.usermode_time) AS max_total_cpu
FROM sys.dm_os_threads t
LEFT JOIN sys.dm_os_workers w ON t.worker_address = w.thread_address
LEFT JOIN sys.dm_os_tasks tk ON w.worker_address = tk.worker_address
GROUP BY tk.task_state
ORDER BY thread_count DESC;

-- Correlate high CPU threads with active sessions
-- Join path: sys.dm_os_threads -> sys.dm_os_workers -> sys.dm_os_tasks -> sys.dm_exec_requests
SELECT 
    t.os_thread_id,
    t.worker_address,
    t.kernel_time + t.usermode_time AS total_cpu_time,
    r.session_id,
    r.request_id,
    r.command,
    r.wait_type,
    r.cpu_time AS request_cpu_time
FROM sys.dm_os_threads t
    INNER JOIN sys.dm_os_workers w ON t.worker_address = w.thread_address
    LEFT JOIN sys.dm_os_tasks tk ON w.worker_address = tk.worker_address
    LEFT JOIN sys.dm_exec_requests r ON tk.task_address = r.task_address
WHERE (t.kernel_time + t.usermode_time) > 1000000  -- High CPU threshold
ORDER BY total_cpu_time DESC;

-- Monitor thread count and task-state distribution over time
-- task_state comes from sys.dm_os_tasks; threads without an active task show NULL state
WITH ThreadStats AS (
    SELECT 
        COUNT(*) AS total_threads,
        SUM(CASE WHEN tk.task_state = 'RUNNING' THEN 1 ELSE 0 END) AS running_tasks,
        SUM(CASE WHEN tk.task_state = 'RUNNABLE' THEN 1 ELSE 0 END) AS runnable_tasks,
        SUM(CASE WHEN tk.task_state = 'SUSPENDED' THEN 1 ELSE 0 END) AS suspended_tasks,
        GETDATE() AS sample_time
    FROM sys.dm_os_threads t
    LEFT JOIN sys.dm_os_workers w ON t.worker_address = w.thread_address
    LEFT JOIN sys.dm_os_tasks tk ON w.worker_address = tk.worker_address
)
SELECT * FROM ThreadStats;

-- Identify potential thread leaks or abnormal thread counts
SELECT 
    'Thread Count Analysis' AS analysis_type,
    COUNT(*) AS current_thread_count,
    (SELECT value FROM sys.configurations WHERE name = 'max worker threads') AS max_worker_threads,
    (COUNT(*) * 100.0) / (SELECT value FROM sys.configurations WHERE name = 'max worker threads') AS thread_utilization_pct
FROM sys.dm_os_threads
UNION ALL
SELECT 
    'High Kernel Time Threads',
    COUNT(*),
    NULL,
    NULL
FROM sys.dm_os_threads
WHERE kernel_time > usermode_time AND (kernel_time + usermode_time) > 500000;

Reconfigure max worker threads for optimal thread pool sizing:

-- Calculate recommended max worker threads based on CPU count
-- Test this configuration change during maintenance windows
DECLARE @cpu_count INT = (SELECT cpu_count FROM sys.dm_os_sys_info);
DECLARE @recommended_threads INT = CASE 
    WHEN @cpu_count <= 4 THEN 512
    WHEN @cpu_count <= 8 THEN 576 + ((@cpu_count - 4) * 16)
    ELSE 576 + (4 * 16) + ((@cpu_count - 8) * 8)
END;

PRINT 'Current CPU Count: ' + CAST(@cpu_count AS VARCHAR(10));
PRINT 'Recommended max worker threads: ' + CAST(@recommended_threads AS VARCHAR(10));

-- Uncomment to apply configuration change
-- EXEC sp_configure 'max worker threads', @recommended_threads;
-- RECONFIGURE;

Kill runaway threads consuming excessive CPU:

-- Identify and terminate sessions with threads consuming excessive CPU
-- WARNING: This will terminate active user sessions
DECLARE @threshold BIGINT = 30000000; -- 30 seconds of CPU time

SELECT 
    'KILL ' + CAST(r.session_id AS VARCHAR(10)) AS kill_command,
    r.session_id,
    t.os_thread_id,
    (t.kernel_time + t.usermode_time) AS total_cpu_time,
    r.command,
    s.program_name,
    s.host_name
FROM sys.dm_os_threads t
    INNER JOIN sys.dm_os_workers w ON t.worker_address = w.thread_address
    INNER JOIN sys.dm_os_tasks tk ON w.worker_address = tk.worker_address
    INNER JOIN sys.dm_exec_requests r ON tk.task_address = r.task_address
    INNER JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE (t.kernel_time + t.usermode_time) > @threshold
    AND r.session_id > 50  -- Exclude system sessions
ORDER BY total_cpu_time DESC;

-- Execute individual KILL commands manually after reviewing

Monitor and alert on thread pool exhaustion:

-- Create monitoring query for thread pool health
-- Run this periodically through SQL Agent job
DECLARE @current_threads INT;
DECLARE @max_threads INT;
DECLARE @utilization_pct DECIMAL(5,2);

SELECT @current_threads = COUNT(*) FROM sys.dm_os_threads;
SELECT @max_threads = value FROM sys.configurations WHERE name = 'max worker threads';
SET @utilization_pct = (@current_threads * 100.0) / @max_threads;

IF @utilization_pct > 85
BEGIN
    DECLARE @msg NVARCHAR(500) = 'Thread pool utilization at ' + 
        CAST(@utilization_pct AS VARCHAR(10)) + '% (' + 
        CAST(@current_threads AS VARCHAR(10)) + '/' + 
        CAST(@max_threads AS VARCHAR(10)) + ')';
    
    -- Replace with your alerting mechanism
    RAISERROR(@msg, 16, 1) WITH LOG;
    -- EXEC msdb.dbo.sp_send_dbmail @subject='Thread Pool Alert', @body=@msg;
END

Configure max worker threads based on CPU core count and workload characteristics rather than accepting defaults. Monitor thread pool utilization consistently, maintaining usage below 80% during peak periods. Implement connection pooling in applications to reduce thread churn and prevent thread exhaustion scenarios.

Set appropriate MAXDOP values to prevent individual queries from consuming excessive worker threads. Configure cost threshold for parallelism above 50 to reduce unnecessary parallel plan generation. Monitor THREADPOOL wait statistics daily and correlate with thread count trends.

Establish baseline measurements for normal thread counts per server role. Development servers typically run 200-400 threads, while high-concurrency OLTP systems may sustain 1000+ threads. Document thread count patterns during peak business hours versus off-peak periods.

Implement query timeout settings in applications to prevent runaway queries from holding threads indefinitely. Use Resource Governor to limit CPU usage for specific workload groups, preventing individual applications from monopolizing thread resources. Configure SQL Server memory settings properly to reduce buffer pool pressure that can cause excessive I/O threads.