Medir latencia de E/S en SQL Server por intervalos

Un archivo con cuatro milisegundos de latencia media desde el arranque puede sufrir un problema grave durante el incidente actual. También una antigua ventana de mantenimiento lenta puede mantener alta la media mucho después. Los contadores de E/S son acumulativos, por lo que una única muestra rara vez explica lo que sucede ahora.

Mide un intervalo definido y relaciónalo con la carga activa en ese periodo. Separa lecturas de datos, escrituras de datos y escrituras del registro. Sus patrones y consecuencias sobre la aplicación son distintos.

Calcular diferencias antes de dividir

El diagnóstico de solo lectura toma dos muestras separadas por cinco segundos y crea únicamente una tabla temporal local. Usa los permisos de diagnóstico adecuados para la versión instalada.

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 divide el incremento del tiempo de espera de lectura entre el incremento de lecturas completadas. AverageWriteMs hace lo mismo con escrituras. Multiplicar por 1.0 evita división entera. NULL significa que no hubo operaciones correspondientes, no almacenamiento con latencia cero.

Lee siempre el número de operaciones junto al promedio. Cien milisegundos basados en una lectura no tienen el mismo peso que sobre miles. Cinco segundos sirven de demostración, no como intervalo universal. Recoge varias muestras durante la lentitud y en un periodo normal comparable.

Los filtros descartan disminuciones evidentes de contadores e intervalos inválidos. No hacen fiables comparaciones que atraviesan reinicios, sustitución de archivos o cambios de ciclo de vida de bases. Un recolector duradero debe registrar inicio de instancia e identidad de archivos y descartar esas discontinuidades. No reinicies contadores compartidos solo para facilitar cálculos.

Los promedios también ocultan distribuciones. Muchas operaciones rápidas y unas pocas muy lentas pueden dar lo mismo que un almacenamiento uniformemente mediocre. Estos contadores no proporcionan percentiles exactos ni identifican la consulta responsable de cada espera.

Relacionar latencia y síntomas

Lecturas lentas pueden acompañar esperas PAGEIOLATCH, pero un exceso de lecturas físicas puede deberse al diseño de consultas o a poca residencia en caché. Un recorrido innecesariamente amplio presiona incluso almacenamiento sano. Revisa acceso y volumen antes de concluir que solo hacen falta discos más rápidos.

Escrituras lentas del registro pueden alargar COMMIT y aparecer junto a WRITELOG. Muchas transacciones pequeñas pueden ser sensibles a la latencia aunque transfieran pocos datos. Las escrituras de archivos de datos se relacionan de otra manera con checkpoints y vaciado en segundo plano. No mezcles todos esos caminos en una puntuación única.

Añade tasa de operaciones, bytes transferidos y tiempos de aplicación. Saturar un límite de transferencia no es lo mismo que pocas operaciones esperando individualmente. Correlaciona sistema operativo y plataforma durante el mismo intervalo.

Distingue PAGEIOLATCH de PAGELATCH. La segunda espera corresponde a un latch en memoria, no a E/S. Nombres parecidos no implican causas iguales; comprar almacenamiento no resuelve una página de asignación en memoria muy disputada.

Elegir el siguiente cambio con evidencia

Identifica archivos y trabajos que cambiaron al empezar el incidente. Una copia, operación de índices, carga o informe ahora solapados pueden generar contención nueva. La infraestructura virtual compartida también puede imponer límites fuera del motor.

Evita declarar un umbral universal aceptable. Importan el objetivo del servicio, tamaño de operación, colas, arquitectura y ruta de durabilidad del registro. Compara con una situación sana de la misma carga y con la capacidad esperada.

Si una reescritura reduce lecturas, repite el mismo informe y observa duración y trabajo total. Si cambia el almacenamiento, valida también commits y tiempos de aplicación. Una media mejor solo sirve si explica una mejora real.

Conserva muestras, fechas, contexto y decisión. Eso permite repetir el diagnóstico y evita convertir un promedio acumulado antiguo en una condena permanente sin fundamento sobre el almacenamiento.

Referencias técnicas: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.