Parcourir les aide-mémoire · SQL Server

Technologie: SQL ServerCatégorie: PerformanceNiveau: IntermédiaireMis à jour

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
  1. Problème
  2. Réponse rapide
  3. Code / commandes
  4. 1. Afficher les principales attentes cumulées
  5. 2. Exclure les attentes inactives et d’arrière-plan courantes pour le diagnostic
  6. 3. Afficher ce qui attend en ce moment
  7. 4. Relier les attentes en cours aux requêtes et au texte SQL
  8. 5. Afficher les statistiques d’attente d’une session
  9. 6. Vérifier l’heure de démarrage de SQL Server
  10. 7. Capturer une référence sans effacer les attentes
  11. 8. Réinitialiser les statistiques d’attente uniquement pour une raison délibérée
  12. Interpréter le résultat
  13. Points à vérifier
  14. Temps d’attente de signal élevé
  15. LCK_M_*
  16. PAGEIOLATCH_*
  17. PAGELATCH_*
  18. SOS_SCHEDULER_YIELD
  19. CXPACKET / CXCONSUMER
  20. WRITELOG
  21. ASYNC_NETWORK_IO
  22. RESOURCE_SEMAPHORE
  23. THREADPOOL
  24. HADR_*
  25. Notes de production
  26. Les statistiques d’attente montrent des symptômes, pas des causes racines automatiques
  27. sys.dm_os_wait_stats montre les attentes terminées
  28. Les statistiques d’attente sont réinitialisées au redémarrage ou par un effacement explicite
  29. Les autorisations varient selon la version
  30. Les attentes d’arrière-plan varient selon l’environnement
  31. Les totaux d’attente exigent une fenêtre temporelle
  32. Erreurs fréquentes
  33. 1. Considérer l’attente principale comme la cause racine
  34. 2. Effacer les statistiques d’attente avant de les capturer
  35. 3. Utiliser une immense liste d’exclusion universelle
  36. 4. Confondre PAGEIOLATCH_* et PAGELATCH_*
  37. 5. Supposer que les attentes de parallélisme signifient « régler MAXDOP sur 1 »
  38. 6. Ignorer les attentes de signal
  39. 7. Ne regarder que les attentes de l’ensemble de l’instance
  40. 8. Comparer des environnements sans normaliser la charge de travail et la durée
  41. Étape suivante
  42. 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_ms inclut le temps d’attente de signal
  • signal_wait_time_ms est 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 page
  • PAGELATCH_* → 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.

Étape suivante

Utilisez le profil d’attente dominant pour choisir le prochain diagnostic.

  • LCK_M_* → examinez les blocages, les bloqueurs principaux et les transactions ouvertes.
  • Temps d’attente de signal élevé / SOS_SCHEDULER_YIELD → examinez la pression sur le CPU et le planificateur.
  • PAGEIOLATCH_* → examinez la latence d’E/S des fichiers, la pression sur la mémoire, les analyses et les chemins d’accès des requêtes.
  • PAGELATCH_* → examinez la contention de latch en mémoire, y compris les profils liés à tempdb le cas échéant.
  • WRITELOG → examinez la latence du journal des transactions, la taille des transactions et les modes de commit.
  • ASYNC_NETWORK_IO → examinez la taille des jeux de résultats, la consommation côté client et le comportement du réseau.
  • RESOURCE_SEMAPHORE → examinez les octrois de mémoire et la cardinalité des requêtes.
  • THREADPOOL → examinez d’urgence la concurrence, les blocages et l’épuisement des workers.
  • Attentes de parallélisme → examinez le déséquilibre des plans, la pression sur le CPU et la charge de travail avant de modifier MAXDOP.
  • Rien de clairement anormal → comparez les deltas d’attente sur une fenêtre temporelle connue et poursuivez avec Query Store et l’analyse des requêtes actives.

← Tous les aide-mémoire