LLI Wiki — My Digital Knowledge Base

Max worker threads and SQL Agent jobs waiting for a worker thread

How to compute the default max worker threads, check their usage with DMVs, and size the worker threads an availability group needs.

A SQL Server Agent job can wait for a worker thread when the instance has run out of worker threads, the threads SQL Server uses to run tasks. This article gives the default maximum, the queries that show worker thread usage, and the worker threads an availability group needs.

Default maximum worker threads

The default depends on the number of logical CPUs:

Logical CPU count Older 32-bit servers Latest 64-bit servers
Up to 4 256 512
8 288 576
16 352 704
32 480 960
64 736 1472
128 4224 4480
256 8320 8576

Check the current maximum and the CPU count:

SELECT max_workers_count, cpu_count FROM sys.dm_os_sys_info;

Check the worker thread usage

DECLARE @max INT;
SELECT @max = max_workers_count
FROM sys.dm_os_sys_info;
SELECT GETDATE() AS 'CurrentDate',
       @max AS 'TotalThreads',
       SUM(active_Workers_count) AS 'CurrentThreads',
       @max - SUM(active_Workers_count) AS 'AvailableThreads',
       SUM(runnable_tasks_count) AS 'WorkersWaitingForCpu',
       SUM(work_queue_count) AS 'RequestWaitingForThreads',
       SUM(current_workers_count) AS 'AssociatedWorkers'
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE';

Usage per scheduler:

SELECT scheduler_id, current_tasks_count, current_workers_count, active_workers_count, work_queue_count
FROM sys.dm_os_schedulers
WHERE status = 'Visible Online';

Break down the workers by state and wait type

SELECT is_preemptive, state, last_wait_type, COUNT(*) AS NumWorkers
FROM sys.dm_os_workers
GROUP BY state, last_wait_type, is_preemptive
ORDER BY COUNT(*) DESC;

Find the system tasks that use worker threads

SELECT
    s.session_id,
    r.command,
    r.status,
    r.wait_type,
    r.scheduler_id,
    w.worker_address,
    w.is_preemptive,
    w.state,
    t.task_state,
    t.session_id,
    t.exec_context_id,
    t.request_id
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r
    ON s.session_id = r.session_id
INNER JOIN sys.dm_os_tasks AS t
    ON r.task_address = t.task_address
INNER JOIN sys.dm_os_workers AS w
    ON t.worker_address = w.worker_address
WHERE s.is_user_process = 0;

Raise the maximum

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'max worker threads', 1000;
GO
RECONFIGURE;
GO

Find when the query processing workers were exhausted

The system_health extended events session records the worker pool state of the QUERY_PROCESSING component. The following queries extract it from the event files.

DROP TABLE dataimport;
SELECT [object_name],
       CAST(event_data AS XML) AS c1
INTO dataimport
FROM sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\system_health*.xel', NULL, NULL, NULL);
GO
SELECT c1.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') AS SdComponent,
       c1
INTO ServerDiagnosticsdata
FROM dataimport
WHERE object_name = 'sp_server_diagnostics_component_result';
CREATE TABLE [dbo].[QryProcessingOutput]
(
    [c1] [XML] NULL
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
INSERT INTO QryProcessingOutput (c1)
SELECT c1 AS snodes
FROM ServerDiagnosticsdata
WHERE SdComponent = 'QUERY_PROCESSING';
SELECT c1.value('(./event/@timestamp)[1]', 'datetime') AS utctimestamp,
       DATEADD(hh, +5.30, c1.value('(./event/@timestamp)[1]', 'datetime')) AS [timestamp],
       c1.value('(event/data[@name="component"]/text)[1]', 'varchar(100)') AS [component_name],
       c1.value('(event/data[@name="state"]/text)[1]', 'varchar(100)') AS [component_state],
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@maxWorkers)[1]', 'int') AS maxworkers,
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@workersCreated)[1]', 'int') AS workerscreated,
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@workersIdle)[1]', 'int') AS workersIdle,
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@tasksCompletedWithinInterval)[1]', 'int') AS tasksCompletedWithinInterval,
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@oldestPendingTaskWaitingTime)[1]', 'bigint') AS oldestPendingTaskWaitingTime,
       c1.value('(./event//data[@name="data"]/value/queryProcessing/@pendingTasks)[1]', 'int') AS pendingTasks
INTO [WorkerThreadData]
FROM ServerDiagnosticsdata;
SELECT [utctimestamp],
       [timestamp],
       [component_name],
       [component_state],
       [maxworkers],
       [workerscreated],
       [workersIdle],
       [tasksCompletedWithinInterval],
       [oldestPendingTaskWaitingTime],
       [pendingTasks]
FROM [WorkerThreadData]
WHERE component_name = 'QUERY_PROCESSING'
  AND workerscreated > maxworkers
ORDER BY [timestamp] DESC;

Availability groups

Each availability group needs worker threads for its own work: one message handler thread per availability group, one log scanner thread per database, and one log send thread per database and per secondary replica. The minimum number of worker threads for an availability group is:

Minimum worker threads (AG) = D × (LCT + LST × SRC) + MHT

where:

Symbol Meaning Value
D Databases in the availability group Count
LCT Log scanner (log capture) thread per database 1
LST Log send thread per database and per secondary replica 1
SRC Secondary replicas Count
MHT Message handler thread per availability group 1

For example, with 100 databases and one secondary replica: 100 × (1 + 1 × 1) + 1 = 201.

SQL Agent subsystems have their own worker thread limit. List the subsystems and their limits:

SELECT s.subsystem_id,
       s.subsystem,
       m.[text] AS [description],
       s.max_worker_threads
FROM msdb.dbo.syssubsystems s
INNER JOIN msdb.sys.messages m
    ON s.description_id = m.message_id;

The source article then changes the limit of subsystem 12:

UPDATE msdb.dbo.syssubsystems
SET max_worker_threads = 5
WHERE subsystem_id = 12;

Notes

References

Mastodon LLIMastodon SocialFosstodon