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 :
- Plus de 4 et jusqu'à 64 processeurs logiques :
512 + ((processeurs logiques - 4) × 16) - Plus de 64 processeurs logiques :
512 + ((processeurs logiques - 4) × 32)
| 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
- Vérifiez le nom du sous-système dans la première requête avant de le modifier. L'exemple utilise l'ID 12 et la valeur 5 de la source ; ce ne sont pas des recommandations.
- Augmenter le maximum est une modification de toute l'instance, et chaque worker thread supplémentaire peut consommer de la mémoire. Comparez les requêtes d'utilisation avant et après la modification.
- Les formules et le tableau des valeurs par défaut viennent des références ci-dessous. Vérifiez-les dans la documentation Microsoft de votre version de SQL Server.
- La colonne d'horodatage ajoute un décalage horaire fixe, écrit
+5.30dans la source.DATEADDavec la partiehhprend des heures entières :+5.30est donc appliqué comme 5 heures. Adaptez le décalage à votre fuseau horaire, en heures entières. - Le chemin des fichiers
system_healthest celui par défaut d'une instance nomméeMSSQLSERVERsur SQL Server 2019 (MSSQL15). Adaptez-le à votre version et à votre instance.