Mesurer la latence d'E/S SQL Server sur un intervalle

Une moyenne de lecture de quatre millisecondes depuis le démarrage n'exclut pas un problème de stockage pendant l'incident actuel. À l'inverse, une ancienne maintenance lente peut maintenir cette moyenne élevée bien après sa résolution. Les compteurs d'E/S par fichier sont cumulatifs: une seule mesure décrit rarement ce qui se passe maintenant.

Mesurez une période définie et associez-la à la charge réellement active. Séparez lectures des données, écritures des données et écritures du journal. Leurs conséquences sur le temps de réponse sont différentes.

Calculer les différences avant les moyennes

Ce diagnostic en lecture seule prend deux mesures espacées de cinq secondes et crée uniquement une table temporaire locale. Utilisez les autorisations de diagnostic correspondant à votre version.

SELECT database_id, file_id, sample_ms, num_of_reads, num_of_writes,
       io_stall_read_ms, io_stall_write_ms
INTO #IoBefore
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

WAITFOR DELAY '00:00:05';

SELECT DB_NAME(a.database_id) AS DatabaseName,
       mf.name AS LogicalFileName, mf.type_desc,
       a.sample_ms - b.sample_ms AS SampleMilliseconds,
       a.num_of_reads - b.num_of_reads AS Reads,
       a.num_of_writes - b.num_of_writes AS Writes,
       CAST(1.0 * (a.io_stall_read_ms - b.io_stall_read_ms)
            / NULLIF(a.num_of_reads - b.num_of_reads, 0)
            AS decimal(12,2)) AS AverageReadMs,
       CAST(1.0 * (a.io_stall_write_ms - b.io_stall_write_ms)
            / NULLIF(a.num_of_writes - b.num_of_writes, 0)
            AS decimal(12,2)) AS AverageWriteMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS a
JOIN #IoBefore AS b
  ON b.database_id = a.database_id AND b.file_id = a.file_id
JOIN sys.master_files AS mf
  ON mf.database_id = a.database_id AND mf.file_id = a.file_id
WHERE a.sample_ms > b.sample_ms
  AND a.num_of_reads >= b.num_of_reads
  AND a.num_of_writes >= b.num_of_writes
  AND a.io_stall_read_ms >= b.io_stall_read_ms
  AND a.io_stall_write_ms >= b.io_stall_write_ms
ORDER BY DatabaseName, mf.type_desc, LogicalFileName;
DROP TABLE #IoBefore;

AverageReadMs divise l'augmentation du temps d'attente de lecture par l'augmentation du nombre de lectures terminées. AverageWriteMs fait de même pour les écritures. Le facteur 1.0 évite une division entière. NULL signifie absence d'opération correspondante, pas stockage instantané.

Examinez toujours le nombre d'opérations. Cent millisecondes sur une seule lecture n'ont pas la même portée que sur plusieurs milliers. Cinq secondes servent à la démonstration, pas de durée universellement représentative. Collectez plusieurs périodes pendant la lenteur et pendant un état normal comparable.

Les filtres rejettent les baisses évidentes des compteurs et les intervalles invalides. Ils ne rendent pas fiables les comparaisons traversant redémarrage, remplacement de fichier ou changement de cycle de vie de la base. Un collecteur durable doit conserver l'heure de démarrage et l'identité des fichiers et exclure ces ruptures. Ne réinitialisez pas des compteurs partagés pour simplifier les calculs.

Une moyenne masque aussi la distribution. Beaucoup d'opérations rapides et quelques très lentes peuvent produire le même résultat qu'un stockage uniformément moyen. Ces compteurs ne donnent ni percentile précis ni requête responsable de chaque attente.

Relier la latence aux symptômes

Des lectures lentes peuvent accompagner PAGEIOLATCH, mais des lectures physiques excessives peuvent venir des requêtes ou d'une faible présence en cache. Une requête parcourant trop de données surcharge même un stockage sain. Vérifiez accès et volume avant de conclure que le matériel est la seule réponse.

Des écritures lentes du journal peuvent allonger COMMIT et s'accompagner de WRITELOG. De nombreuses petites transactions peuvent être sensibles à la latence malgré un faible débit. Les écritures de données ont un autre lien avec checkpoints et vidage de fond. Ne fusionnez pas tous ces chemins dans un score unique.

Ajoutez fréquence d'opérations, octets transférés et durée applicative. Une limite de débit saturée diffère d'une faible activité attendant chaque écriture. Corrélez les mesures système et plateforme sur le même intervalle, pas avec des moyennes quotidiennes sans rapport.

Distinguez aussi PAGEIOLATCH de PAGELATCH. Le second désigne une attente de latch en mémoire, pas une E/S. Des noms proches n'impliquent pas une cause identique; acheter du stockage ne répare pas une page d'allocation mémoire trop sollicitée.

Choisir une modification justifiée

Identifiez fichiers et traitements ayant changé au début de l'incident. Sauvegarde, maintenance d'index, chargement ou rapport désormais simultanés peuvent créer une concurrence nouvelle. Une infrastructure virtualisée partagée peut aussi limiter les ressources hors du moteur.

Évitez un seuil universel de millisecondes acceptables. Exigence de service, taille des opérations, files d'attente, architecture et durabilité du journal comptent. Comparez la charge à son propre état sain et aux capacités attendues de la plateforme.

Après une réécriture réduisant les lectures, relancez le même rapport et mesurez durée et travail total. Après une modification du stockage, vérifiez aussi le comportement des commits et de l'application. Une meilleure moyenne n'est utile que si elle explique un bénéfice réel.

Conservez échantillons, horaires, contexte et décision. Vous disposerez ainsi d'un diagnostic reproductible au prochain incident, au lieu de transformer une vieille moyenne cumulée en verdict permanent sur le stockage.

Références techniques: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.