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:
- More than 4 and up to 64 logical CPUs:
512 + ((logical CPUs - 4) × 16) - More than 64 logical CPUs:
512 + ((logical CPUs - 4) × 32)
| 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
- Check the subsystem name in the first query before changing a subsystem. The example uses ID 12 and the value 5 from the source; they are not a recommendation.
- Raising the maximum is an instance-wide change, and each extra worker thread can use memory. Compare the usage queries before and after the change.
- The formulas and the table of defaults come from the references below. Check them against the Microsoft documentation for your SQL Server version.
- The timestamp column adds a fixed time offset, written as
+5.30in the source.DATEADDwith thehhpart takes whole hours, so+5.30is applied as 5 hours. Adapt the offset to your time zone, in whole hours. - The
system_healthfile path is the default for an instance namedMSSQLSERVERon SQL Server 2019 (MSSQL15). Adapt it to your version and instance.