SQL Server 2016-2019修改工作线程前，如何监控线程使用？

Great question—capturing a baseline of worker thread usage before tweaking SQL Server 2016/2019 configurations is absolutely critical to avoid unintended performance issues. Let’s walk through your current approach and then dive into better, more insightful monitoring methods.

Your existing queries get the basics right, but let’s formalize the snapshot process so you can save the state for direct comparison post-change:

-- Create a temporary table to store your pre-change baseline
CREATE TABLE #WorkerThreadBaseline (
    SnapshotTime DATETIME DEFAULT GETDATE(),
    MaxWorkersCount INT,
    CurrentActiveWorkers INT,
    WorkerUsagePercentage REAL
);

-- Insert the baseline data
INSERT INTO #WorkerThreadBaseline (MaxWorkersCount, CurrentActiveWorkers, WorkerUsagePercentage)
SELECT
    i.max_workers_count,
    w.WorkerCount,
    CAST(w.WorkerCount * 100 AS REAL) / i.max_workers_count
FROM sys.dm_os_sys_info i
CROSS APPLY (SELECT COUNT(*) AS WorkerCount FROM sys.dm_os_workers) w;

-- View your baseline
SELECT * FROM #WorkerThreadBaseline;

This gives you a static point-in-time reference to measure against after adjusting worker thread settings.

Counting total workers only tells part of the story—you need to know what those threads are doing. Use sys.dm_os_worker_states to break down threads by their operational state, which is far more useful for identifying bottlenecks:

-- Break down worker threads by state with usage percentages
SELECT
    state AS WorkerState,
    COUNT(*) AS ThreadCount,
    CAST(COUNT(*) * 100 AS REAL) / (SELECT max_workers_count FROM sys.dm_os_sys_info) AS StateUsagePercentage
FROM sys.dm_os_worker_states
GROUP BY state
ORDER BY ThreadCount DESC;

Key states to prioritize:

• RUNNING: Threads actively executing tasks
• SUSPENDED: Threads waiting for resources (e.g., locks, I/O)
• IDLE: Threads available for new work

A high percentage of SUSPENDED threads usually signals underlying resource contention—not just thread exhaustion.

Single snapshots don’t capture peak usage (the exact scenario where thread limits matter most). Set up a recurring SQL Server Agent job to capture historical data:

First, create a persistent table to store history:

CREATE TABLE dbo.WorkerThreadHistory (
    HistoryID INT IDENTITY(1,1) PRIMARY KEY,
    SnapshotTime DATETIME DEFAULT GETDATE(),
    MaxWorkersCount INT,
    TotalWorkers INT,
    RunningWorkers INT,
    SuspendedWorkers INT,
    IdleWorkers INT,
    OverallUsagePercentage REAL
);

Then create a job step with this query to run every 5-10 minutes during business hours:

INSERT INTO dbo.WorkerThreadHistory (
    MaxWorkersCount, TotalWorkers, RunningWorkers, SuspendedWorkers, IdleWorkers, OverallUsagePercentage
)
SELECT
    i.max_workers_count,
    total.Workers,
    running.Workers,
    suspended.Workers,
    idle.Workers,
    CAST(total.Workers * 100 AS REAL) / i.max_workers_count
FROM sys.dm_os_sys_info i
CROSS APPLY (SELECT COUNT(*) AS Workers FROM sys.dm_os_workers) total
CROSS APPLY (SELECT COUNT(*) AS Workers FROM sys.dm_os_worker_states WHERE state = 'RUNNING') running
CROSS APPLY (SELECT COUNT(*) AS Workers FROM sys.dm_os_worker_states WHERE state = 'SUSPENDED') suspended
CROSS APPLY (SELECT COUNT(*) AS Workers FROM sys.dm_os_worker_states WHERE state = 'IDLE') idle;

This lets you spot trends, peak usage times, and whether your current thread limit is actually being pushed to its limits.

If you need to identify which workloads are consuming threads, join worker thread DMVs with session and query data:

-- Map worker threads to active user sessions and queries
SELECT
    s.session_id,
    s.login_name,
    wt.state AS WorkerState,
    t.task_state,
    SUBSTRING(qt.text, (r.statement_start_offset/2)+1, 
        ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE r.statement_end_offset END - r.statement_start_offset)/2)+1) AS ActiveQuery
FROM sys.dm_os_workers wt
JOIN sys.dm_os_tasks t ON wt.worker_address = t.worker_address
JOIN sys.dm_exec_sessions s ON t.session_id = s.session_id
LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) qt
WHERE s.is_user_process = 1 -- Filter to user sessions
ORDER BY wt.state DESC;

This helps you pinpoint problematic queries or sessions that are hogging worker threads.

For granular, low-overhead tracking of thread creation/deletion, use SQL Server Extended Events:

-- Create an extended events session to monitor worker thread lifecycle
CREATE EVENT SESSION WorkerThreadLifecycle ON SERVER
ADD EVENT sqlos.worker_thread_creation,
ADD EVENT sqlos.worker_thread_deletion
ADD TARGET package0.event_file(SET filename=N'C:\SQLLogs\WorkerThreadLifecycle.xel') -- Update path to your log directory
WITH (STARTUP_STATE=OFF);

Start the session during peak hours, then analyze the events later:

SELECT
    event_data.value('(event/@name)[1]', 'varchar(50)') AS EventType,
    event_data.value('(event/@timestamp)[1]', 'datetime') AS EventTime,
    event_data.value('(event/data[@name="worker_address"]/value)[1]', 'varchar(64)') AS WorkerAddress
FROM (
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('C:\SQLLogs\WorkerThreadLifecycle*.xel', NULL, NULL, NULL)
) AS xe_data;

This gives you a play-by-play of how threads are being spun up and torn down, which is invaluable for understanding workload dynamics.

内容的提问来源于stack exchange，提问作者TechnoCaveman_user3393873