Mesurer les attentes SQL Server pendant un incident

La plus grande attente cumulée du serveur n'explique pas forcément le ralentissement apparu à 10 h 15. Elle peut inclure des semaines de maintenance, de tâches de fond et une ancienne charge. Pour un incident court, mesurez ce qui change dans l'intervalle pertinent, puis reliez cette évolution aux requêtes concernées.

Comparer les relevés sans effacer les compteurs

Le script prend deux instantanés espacés de dix secondes et soustrait les compteurs cumulatifs. Il ne remet pas les statistiques partagées à zéro. Utilisez les droits adaptés à votre version : généralement VIEW SERVER STATE avant SQL Server 2022, puis VIEW SERVER PERFORMANCE STATE à partir de 2022.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #WaitBefore
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT
    w.wait_type,
    w.waiting_tasks_count - b.waiting_tasks_count AS waits_started,
    w.wait_time_ms - b.wait_time_ms AS wait_ms,
    w.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms,
    (w.wait_time_ms - b.wait_time_ms)
      - (w.signal_wait_time_ms - b.signal_wait_time_ms) AS resource_ms
INTO #WaitDelta
FROM sys.dm_os_wait_stats AS w
JOIN #WaitBefore AS b ON b.wait_type = w.wait_type;

IF EXISTS
(
    SELECT 1 FROM #WaitDelta
    WHERE waits_started < 0 OR wait_ms < 0 OR signal_ms < 0
)
    THROW 50001, 'Counters changed incompatibly; discard this sample.', 1;

SELECT TOP (20) *
FROM #WaitDelta
WHERE wait_ms > 0
ORDER BY wait_ms DESC;

DROP TABLE #WaitDelta;
DROP TABLE #WaitBefore;

WAITFOR définit simplement l'intervalle et ajoute sa propre attente. La démonstration conserve donc toutes les catégories au lieu de cacher une liste arbitraire. Dans un collecteur permanent, enregistrez l'heure UTC, l'identité du serveur et l'heure de démarrage du moteur. Des relevés planifiés séparés évitent généralement de garder une session en attente.

Une différence négative indique que les instantanés ne sont pas comparables, souvent après une remise à zéro. Un redémarrage coupe normalement aussi cette session et ses tables temporaires. Un collecteur persistant doit, lui, détecter explicitement le nouveau démarrage.

Le contrôle simple ne détecte pas toutes les réinitialisations : un compteur peut être effacé puis dépasser son ancienne valeur avant le second relevé. Coordonnez les pratiques de remise à zéro. Ne soustrayez pas non plus max_wait_time_ms pour calculer un maximum d'intervalle. Si le maximum historique reste à 20 secondes, une nouvelle attente de 19 secondes ne modifie pas ce maximum.

Comprendre les unités avant les pourcentages

Le temps total inclut le temps de signal. Leur différence correspond à la composante d'attente de ressource représentée par ces compteurs. Le signal mesure le délai entre la disponibilité pour exécuter et l'exécution effective. Une valeur élevée demande une corrélation avec le travail exécutable et la demande CPU, pas une conclusion automatique.

Les millisecondes cumulées sont du temps de workers, pas du temps écoulé. Dix tâches attendant chacune une seconde peuvent ajouter environ dix secondes durant une seconde d'observation. La concurrence et le parallélisme produisent donc des totaux supérieurs à la fenêtre. Les diviser par sa durée ne donne pas un taux d'utilisation classique.

Le nombre d'attentes et leur durée franchissent aussi différemment les frontières de l'échantillon. Le nombre augmente au démarrage, tandis que la durée terminée apparaît plus tard. Une moyenne calculée sur un très court intervalle peut être trompeuse ou impossible lorsque le dénominateur est nul. Pour une distribution précise, capturez des événements individuels.

Certaines attentes décrivent une coordination normale de fond. Les filtrer rend un tableau de bord plus lisible, mais conservez les données brutes et la définition du filtre. Une catégorie inutile aujourd'hui peut devenir pertinente demain. Supprimer une catégorie change également les pourcentages des autres sans modifier leur travail réel.

Retrouver la charge responsable

Une hausse des attentes de verrous oriente vers les bloqueurs, l'âge des transactions et les objets concernés. PAGEIOLATCH mène aux lectures de pages et au volume demandé. PAGELATCH concerne la synchronisation en mémoire et ne justifie pas automatiquement un investissement disque. ASYNC_NETWORK_IO peut refléter une consommation lente par le client.

Pendant un blocage actif, consultez sys.dm_os_waiting_tasks et les requêtes courantes. Une longue attente encore en cours peut ne pas être entièrement représentée dans les durées terminées. Sauvegardez la chaîne de blocage et les identifiants tant que les transactions existent.

Validez ensuite la correction sur une période comparable. Associez aux différences le nombre de requêtes, le mélange d'opérations métier et la latence utilisateur. Moins d'attentes lorsque le serveur est simplement moins chargé ne prouve rien. La conclusion utile explique quelle activité était freinée et montre que le même travail se termine désormais avec moins de délai.

Références techniques: Microsoft Learn: Wait statistics · Microsoft Learn: Waiting tasks.