Parcourir les aide-mémoire · SQL Server
Types d’attente SQL Server
Utilisez les statistiques d’attente SQL Server pour identifier où le travail passe son temps, distinguer les attentes en cours des attentes cumulées et choisir la prochaine piste d’investigation des performances.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Afficher les principales attentes cumulées
- 2. Exclure les attentes inactives et d’arrière-plan courantes pour le diagnostic
- 3. Afficher ce qui attend en ce moment
- 4. Relier les attentes en cours aux requêtes et au texte SQL
- 5. Afficher les statistiques d’attente d’une session
- 6. Vérifier l’heure de démarrage de SQL Server
- 7. Capturer une référence sans effacer les attentes
- 8. Réinitialiser les statistiques d’attente uniquement pour une raison délibérée
- Interpréter le résultat
- Points à vérifier
- Temps d’attente de signal élevé
- LCK_M_*
- PAGEIOLATCH_*
- PAGELATCH_*
- SOS_SCHEDULER_YIELD
- CXPACKET / CXCONSUMER
- WRITELOG
- ASYNC_NETWORK_IO
- RESOURCE_SEMAPHORE
- THREADPOOL
- HADR_*
- Notes de production
- Les statistiques d’attente montrent des symptômes, pas des causes racines automatiques
- sys.dm_os_wait_stats montre les attentes terminées
- Les statistiques d’attente sont réinitialisées au redémarrage ou par un effacement explicite
- Les autorisations varient selon la version
- Les attentes d’arrière-plan varient selon l’environnement
- Les totaux d’attente exigent une fenêtre temporelle
- Erreurs fréquentes
- 1. Considérer l’attente principale comme la cause racine
- 2. Effacer les statistiques d’attente avant de les capturer
- 3. Utiliser une immense liste d’exclusion universelle
- 4. Confondre PAGEIOLATCH_* et PAGELATCH_*
- 5. Supposer que les attentes de parallélisme signifient « régler MAXDOP sur 1 »
- 6. Ignorer les attentes de signal
- 7. Ne regarder que les attentes de l’ensemble de l’instance
- 8. Comparer des environnements sans normaliser la charge de travail et la durée
- Étape suivante
- Pour aller plus loin
Problème
SQL Server est lent, mais le CPU, les lectures, les blocages et la durée des requêtes n’expliquent pas immédiatement pourquoi.
Les statistiques d’attente aident à répondre à une autre question :
Qu’est-ce que SQL Server passe son temps à attendre ?
L’objectif n’est pas de trouver un « mauvais » type d’attente et de le corriger à l’aveugle.
L’objectif est de :
- identifier les catégories d’attente dominantes
- distinguer les attentes en cours des attentes cumulées historiques
- séparer les attentes de ressources des attentes de file d’attente et d’arrière-plan
- déterminer si le goulot d’étranglement relève probablement du CPU, du verrouillage, des E/S, de la mémoire, du parallélisme, de la journalisation, du réseau ou d’autre chose
- choisir la prochaine étape de diagnostic
Réponse rapide
Utilisez sys.dm_os_wait_stats pour l’historique cumulé des attentes au niveau de l’instance.
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
CAST(
(wait_time_ms - signal_wait_time_ms) / 1000.0
AS decimal(18,2)
) AS resource_wait_seconds,
CAST(
signal_wait_time_ms / 1000.0
AS decimal(18,2)
) AS signal_wait_seconds,
CAST(
100.0 * wait_time_ms
/ NULLIF(SUM(wait_time_ms) OVER (), 0)
AS decimal(6,2)
) AS pct_of_total_wait_time
FROM sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Utilisez cette requête pour trouver les attentes dominantes depuis la dernière réinitialisation des statistiques ou le dernier démarrage du moteur SQL Server.
Utilisez ensuite sys.dm_os_waiting_tasks lorsque vous devez savoir ce qui attend en ce moment même.
Code / commandes
1. Afficher les principales attentes cumulées
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
CAST(
wait_time_ms * 1.0
/ NULLIF(waiting_tasks_count, 0)
AS decimal(18,2)
) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE
wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Utilisez cette requête comme point de départ général au niveau de l’instance.
Rappel :
wait_time_msinclut le temps d’attente de signalsignal_wait_time_msest le temps écoulé entre le moment où un worker est signalé et celui où il obtient réellement le CPU- le temps d’attente de ressource correspond approximativement à :
wait_time_ms - signal_wait_time_ms
2. Exclure les attentes inactives et d’arrière-plan courantes pour le diagnostic
Certaines attentes sont normales et peuvent dominer les totaux sans révéler de goulot d’étranglement visible par les utilisateurs.
Utilisez une vue filtrée pour un diagnostic concret.
SELECT TOP (30)
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE
wait_type NOT IN
(
N'BROKER_EVENTHANDLER',
N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH',
N'CHECKPOINT_QUEUE',
N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT',
N'DBMIRROR_DBM_EVENT',
N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE',
N'DIRTY_PAGE_POLL',
N'DISPATCHER_QUEUE_SEMAPHORE',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'HADR_CLUSAPI_CALL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK',
N'HADR_WORK_QUEUE',
N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE',
N'ONDEMAND_TASK_QUEUE',
N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK',
N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY',
N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED',
N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK',
N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP',
N'SNI_HTTP_ACCEPT',
N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'WAITFOR',
N'WAITFOR_TASKSHUTDOWN',
N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT',
N'XE_TIMER_EVENT'
)
AND wait_time_ms > 0
ORDER BY
wait_time_ms DESC;
Ne considérez aucune « liste d’exclusion » comme universelle.
Les bonnes exclusions dépendent :
- de la version de SQL Server
- des fonctionnalités activées
- des Availability Groups
- de Service Broker
- de Query Store
- de la réplication
- du type de charge de travail
Filtrez pour réduire le bruit, pas pour masquer les attentes que vous ne connaissez pas.
3. Afficher ce qui attend en ce moment
sys.dm_os_wait_stats est un historique cumulé.
Pour les attentes en cours, utilisez :
SELECT
wt.session_id,
wt.exec_context_id,
wt.wait_duration_ms,
wt.wait_type,
wt.blocking_session_id,
wt.resource_description
FROM sys.dm_os_waiting_tasks AS wt
ORDER BY
wt.wait_duration_ms DESC;
C’est la bonne requête lorsque le problème de performance se produit en ce moment même.
4. Relier les attentes en cours aux requêtes et au texte SQL
SELECT
wt.session_id,
wt.wait_duration_ms,
wt.wait_type,
wt.blocking_session_id,
wt.resource_description,
r.status,
r.command,
DB_NAME(r.database_id) AS database_name,
r.cpu_time,
r.logical_reads,
txt.text AS batch_text
FROM sys.dm_os_waiting_tasks AS wt
LEFT JOIN sys.dm_exec_requests AS r
ON r.session_id = wt.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
ORDER BY
wt.wait_duration_ms DESC;
Cette requête rend un type d’attente exploitable en montrant :
- quelle session attend
- quel SQL elle exécute
- si une autre session la bloque
- quelle base de données est concernée
5. Afficher les statistiques d’attente d’une session
Pour l’historique à l’échelle d’une session :
DECLARE @session_id int = 57;
SELECT
session_id,
wait_type,
waiting_tasks_count,
wait_time_ms,
max_wait_time_ms,
signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE
session_id = @session_id
ORDER BY
wait_time_ms DESC;
C’est utile lorsque vous examinez une session connue et souhaitez éviter le bruit de l’ensemble de l’instance.
Les statistiques d’attente de session sont réinitialisées lorsque la session est créée ou réinitialisée, y compris lors de la réinitialisation par le pool de connexions.
6. Vérifier l’heure de démarrage de SQL Server
Les statistiques d’attente ne sont cumulées que depuis la dernière réinitialisation ou le dernier démarrage.
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Associez toujours les statistiques d’attente à leur fenêtre d’observation.
Un total de 10 heures d’attente sur 30 jours n’a pas du tout le même sens que 10 heures sur 20 minutes.
7. Capturer une référence sans effacer les attentes
La pratique la plus sûre en production consiste à prendre des instantanés des statistiques d’attente et à comparer les deltas.
Exemple de premier instantané :
SELECT
SYSDATETIMEOFFSET() AS captured_at,
wait_type,
waiting_tasks_count,
wait_time_ms,
signal_wait_time_ms
FROM sys.dm_os_wait_stats;
Conservez ce résultat dans une table de supervision ou un stockage de télémétrie externe.
Comparez ensuite deux instantanés :
delta_wait_time_ms
= current.wait_time_ms - previous.wait_time_ms
delta_wait_count
= current.waiting_tasks_count - previous.waiting_tasks_count
L’analyse des deltas est bien plus utile que les totaux cumulés depuis le démarrage.
8. Réinitialiser les statistiques d’attente uniquement pour une raison délibérée
SQL Server prend en charge :
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
Ne l’utilisez pas à la légère sur des systèmes de production.
Effacer les statistiques d’attente supprime la référence cumulée existante pour toutes les personnes qui diagnostiquent l’instance.
Préférez une supervision par instantanés et deltas.
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
wait_type |
Nom de la ressource ou de l’événement attendu par les workers SQL Server. |
waiting_tasks_count |
Nombre d’attentes enregistrées pour ce type d’attente. |
wait_time_ms |
Temps d’attente total, temps d’attente de signal inclus. |
signal_wait_time_ms |
Temps passé à l’état exécutable après avoir été signalé, mais avant d’obtenir le CPU. |
resource_wait_time_ms |
Approximation du temps d’attente hors signal : total moins signal. |
avg_wait_ms |
Durée moyenne approximative d’une attente. Utile pour le contexte, pas comme verdict à elle seule. |
blocking_session_id |
Session qui bloque la tâche en attente, le cas échéant. |
resource_description |
Détails supplémentaires sur la ressource attendue. |
Points à vérifier
Temps d’attente de signal élevé
Des attentes de signal élevées peuvent indiquer une pression sur le planificateur CPU.
Vérifications possibles ensuite :
- l’utilisation du CPU du serveur
- le nombre de tâches exécutables
sys.dm_os_schedulers- les requêtes actives coûteuses
- la concurrence
- les charges de travail parallèles
Ne diagnostiquez pas une pression CPU à partir des seules attentes de signal. Corrélez-les avec des données réelles sur le CPU et le planificateur.
LCK_M_*
Ce sont des attentes de verrou.
Étape suivante :
- identifier les sessions bloquées
- identifier le bloqueur principal
- examiner les transactions ouvertes
- examiner la portée des transactions et l’ordre d’accès
Ne traitez pas par défaut une attente de verrou comme un problème d’indexation.
PAGEIOLATCH_*
Ces attentes indiquent que des pages attendent d’être lues depuis le stockage vers la mémoire.
Pistes possibles :
- la latence du stockage
- la pression sur le cache de tampons
- les analyses volumineuses
- des chemins d’accès inefficaces
- une indexation utile insuffisante
- la croissance de la charge de travail
Ne concluez pas que « le disque est lent » sans corréler avec la latence d’E/S au niveau des fichiers et le comportement des requêtes.
PAGELATCH_*
Ce sont des attentes de latch en mémoire, et non des attentes d’E/S classiques sur les fichiers de données.
Elles peuvent révéler une contention sur des structures ou des pages internes en mémoire.
Par exemple, la contention d’allocation dans tempdb peut se traduire par certains profils de latch de page.
Ne confondez pas PAGELATCH_* et PAGEIOLATCH_*.
SOS_SCHEDULER_YIELD
Cette attente indique que des workers ont volontairement cédé le planificateur après avoir consommé leur quantum CPU.
Elle peut être normale sur des systèmes où le CPU est très sollicité.
Si elle domine pendant une dégradation des performances, examinez les requêtes gourmandes en CPU et la pression sur le planificateur.
CXPACKET / CXCONSUMER
Ce sont des attentes liées au parallélisme.
Elles ne signifient pas automatiquement que MAXDOP est mal configuré.
Examinez :
- les plans parallèles déséquilibrés
- la forme de la requête
- les estimations de cardinalité
- la pression sur le CPU
- les opérateurs d’échange
- la concurrence de la charge de travail
Les versions récentes de SQL Server isolent dans CXCONSUMER davantage d’attentes côté consommateur, plus bénignes, ce qui peut rendre CXPACKET plus exploitable que dans les versions antérieures.
WRITELOG
Cette attente signifie que des sessions attendent le vidage du journal des transactions.
Examinez :
- la latence du stockage du journal
- la taille des transactions
- la fréquence des commits
- les charges de travail à forte écriture
- les effets d’une réplication synchrone
- le débit du journal
ASYNC_NETWORK_IO
SQL Server attend que le client consomme les lignes de résultat.
Causes possibles :
- un traitement lent côté client
- des jeux de résultats volumineux
- un délai réseau
- une consommation ligne par ligne par l’application
N’accusez pas immédiatement le réseau.
RESOURCE_SEMAPHORE
Des requêtes attendent l’octroi de mémoire d’exécution (memory grants).
Examinez :
- les octrois de mémoire volumineux
- les requêtes analytiques concurrentes
- les mauvaises estimations de cardinalité
- la forme du plan de requête
- la pression sur la mémoire de travail (workspace memory)
THREADPOOL
SQL Server attend des threads de travail (worker threads).
C’est un signal de concurrence hautement prioritaire.
Facteurs possibles :
- des blocages sévères
- trop de requêtes concurrentes
- des requêtes de longue durée
- un pool de workers épuisé
Ne « corrigez » pas les attentes THREADPOOL en modifiant max worker threads sans comprendre la pression exercée par la charge de travail.
HADR_*
Les attentes liées aux Availability Groups doivent être replacées dans leur contexte.
De nombreuses attentes HADR sont des attentes d’arrière-plan normales.
Si la préoccupation porte sur les performances de la disponibilité ou de la réplication, analysez l’attente HADR précise ainsi que les métriques de file d’attente, de redo et d’envoi de l’AG.
Notes de production
Les statistiques d’attente montrent des symptômes, pas des causes racines automatiques
Un type d’attente vous indique où le temps est passé.
Il ne vous dit pas toujours pourquoi.
Exemple :
PAGEIOLATCH_SH peut être causée par :
- un stockage lent
- une analyse massive
- une pression sur la mémoire
- un index manquant ou inefficace
- une régression de requête
La bonne correction dépend des autres éléments de preuve.
sys.dm_os_wait_stats montre les attentes terminées
Cette DMV est cumulative et ne représente pas uniquement le travail actuellement en attente.
Pour les tâches actuellement en attente, utilisez :
sys.dm_os_waiting_tasks
Les statistiques d’attente sont réinitialisées au redémarrage ou par un effacement explicite
sys.dm_os_wait_stats n’est pas une télémétrie historique durable.
Suivez :
sys.dm_os_sys_info.sqlserver_start_time
et, de préférence, prenez des instantanés des statistiques d’attente dans la durée.
Les autorisations varient selon la version
Pour SQL Server 2019 et les versions antérieures, un accès étendu aux DMV d’attente nécessite généralement :
VIEW SERVER STATE
Pour SQL Server 2022 et les versions ultérieures :
VIEW SERVER PERFORMANCE STATE
Azure SQL Database et les autres plateformes SQL appliquent des règles de portée et d’autorisation différentes.
Les attentes d’arrière-plan varient selon l’environnement
Une liste d’attentes inactives établie sur un serveur peut être trompeuse sur un autre.
Des fonctionnalités telles que :
- les Availability Groups
- Service Broker
- Query Store
- la réplication
- les scripts externes
introduisent des attentes qui peuvent être normales.
Les totaux d’attente exigent une fenêtre temporelle
Ne dites jamais :
« Nous avons 3 heures d’attentes PAGEIOLATCH »
sans préciser également :
« sur quelle période ? »
Le taux et le delta comptent davantage que les totaux depuis le démarrage.
Erreurs fréquentes
1. Considérer l’attente principale comme la cause racine
L’attente cumulée la plus élevée peut simplement refléter la caractéristique la plus durable de l’instance.
Utilisez :
- les deltas sur une fenêtre temporelle
- les attentes en cours
- les éléments de preuve au niveau des requêtes
- les métriques du serveur
avant de décider.
2. Effacer les statistiques d’attente avant de les capturer
Une fois effacée, la référence est perdue.
Capturez d’abord.
3. Utiliser une immense liste d’exclusion universelle
Le comportement des attentes dépend de la version et des fonctionnalités.
Filtrez le bruit d’arrière-plan évident, mais comprenez ce que vous retirez.
4. Confondre PAGEIOLATCH_* et PAGELATCH_*
Elles correspondent à des catégories de problèmes différentes.
PAGEIOLATCH_*→ attente d’E/S de pagePAGELATCH_*→ contention de latch en mémoire
5. Supposer que les attentes de parallélisme signifient « régler MAXDOP sur 1 »
Le parallélisme peut être bénéfique.
Examinez les plans parallèles réels, la pression sur le CPU et le déséquilibre avant de modifier la configuration au niveau du serveur.
6. Ignorer les attentes de signal
Le temps d’attente de signal peut aider à révéler une pression sur la planification CPU.
Mais il doit être corrélé avec les données du planificateur et du CPU.
7. Ne regarder que les attentes de l’ensemble de l’instance
Une seule charge de travail bruyante peut dominer les totaux de l’instance.
Utilisez :
- les attentes de session
- les tâches actuellement en attente
- Query Store
- les diagnostics au niveau des requêtes
pour restreindre le périmètre.
8. Comparer des environnements sans normaliser la charge de travail et la durée
Les totaux d’attente de la production et de l’environnement de test ne sont pas directement comparables lorsque :
- le volume de charge de travail diffère
- la durée de fonctionnement diffère
- le matériel diffère
- les fonctionnalités diffèrent
Comparez des taux et des contextes, pas des totaux bruts.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.