LLI Wiki — My Digital Knowledge Base

Max worker threads et jobs SQL Agent en attente d'un worker thread

Comment calculer le maximum par défaut de worker threads, contrôler leur utilisation avec les DMV et dimensionner ceux requis par un groupe de disponibilité.

Un job SQL Server Agent peut attendre un worker thread lorsque l'instance n'en a plus de disponible : ce sont les threads que SQL Server utilise pour exécuter les tâches. Cet article donne le maximum par défaut, les requêtes qui montrent l'utilisation des worker threads, et le nombre de worker threads dont un groupe de disponibilité a besoin.

Maximum par défaut de worker threads

Le défaut dépend du nombre de processeurs logiques :

Nombre de processeurs logiques Anciens serveurs 32 bits Serveurs 64 bits récents
Jusqu'à 4 256 512
8 288 576
16 352 704
32 480 960
64 736 1472
128 4224 4480
256 8320 8576

Contrôlez le maximum actuel et le nombre de processeurs :

SELECT max_workers_count, cpu_count FROM sys.dm_os_sys_info;

Contrôler l'utilisation des worker threads

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';

Utilisation par 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';

Répartir les workers par état et type d'attente

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;

Trouver les tâches système qui utilisent des 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;

Augmenter le maximum

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

Trouver le moment où les workers de traitement des requêtes ont été épuisés

La session d'événements étendus system_health enregistre l'état du pool de workers du composant QUERY_PROCESSING. Les requêtes suivantes l'extraient des fichiers d'événements.

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;

Groupes de disponibilité

Chaque groupe de disponibilité a besoin de worker threads pour son propre travail : un thread de gestion des messages par groupe, un thread de lecture du journal par base, et un thread d'envoi du journal par base et par réplica secondaire. Le nombre minimal de worker threads d'un groupe de disponibilité est :

Worker threads minimum (AG) = D × (LCT + LST × SRC) + MHT

où :

Symbole Signification Valeur
D Bases dans le groupe de disponibilité Nombre
LCT Thread de lecture du journal (log capture) par base 1
LST Thread d'envoi du journal par base et par réplica secondaire 1
SRC Réplicas secondaires Nombre
MHT Thread de gestion des messages par groupe de disponibilité 1

Par exemple, avec 100 bases et un réplica secondaire : 100 × (1 + 1 × 1) + 1 = 201.

Les sous-systèmes de SQL Agent ont leur propre limite de worker threads. Listez les sous-systèmes et leurs limites :

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;

La source modifie ensuite la limite du sous-système 12 :

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

Remarques

Références

Mastodon LLIMastodon SocialFosstodon