Calculate read and write latency per file

Waits point to entry and exit or multiple bases share storage. We need to compare how long reads and writes wait for each file based on the counters accumulated by SQL Server.

What we need to distinguish

Input/output—I/O—is the set of read and write operations against storage. Latency is the time they take; a cumulative average divides total time by the operations recorded since the counter started.

The average latency is obtained by dividing accumulated waiting time between operations. A file with few operations may show an extreme average, and the counter does not automatically separate storage, queue, operating system, or load pressure.

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    DB_NAME(vfs.database_id) AS BaseDatos,
    mf.name AS ArchivoLogico,
    mf.type_desc AS TipoArchivo,
    mf.physical_name AS RutaFisica,
    vfs.num_of_reads AS Lecturas,
    CONVERT
    (
        DECIMAL(18, 2),
        vfs.num_of_bytes_read / 1048576.0
    ) AS LeidoMb,
    CONVERT
    (
        DECIMAL(18, 2),
        1.0 * vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)
    ) AS LatenciaLecturaMs,
    vfs.num_of_writes AS Escrituras,
    CONVERT
    (
        DECIMAL(18, 2),
        vfs.num_of_bytes_written / 1048576.0
    ) AS EscritoMb,
    CONVERT
    (
        DECIMAL(18, 2),
        1.0 * vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)
    ) AS LatenciaEscrituraMs,
    CONVERT
    (
        DECIMAL(18, 2),
        1.0 * vfs.io_stall
        / NULLIF(vfs.num_of_reads + vfs.num_of_writes, 0)
    ) AS LatenciaPromedioMs,
    vfs.sample_ms AS VentanaMuestraMs
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
INNER JOIN sys.master_files AS mf
    ON mf.database_id = vfs.database_id
   AND mf.file_id = vfs.file_id
WHERE vfs.num_of_reads + vfs.num_of_writes > 0
ORDER BY
    LatenciaPromedioMs DESC,
    vfs.num_of_reads + vfs.num_of_writes DESC;
GO

Parameters or permissions

The two NULL values request all files in the instance. VIEW SERVER STATE is required up to SQL Server 2019, or VIEW SERVER PERFORMANCE STATE from SQL Server 2022. The counters start over when the service starts. SampleWindow helps compare screenshots from the same server; To measure a specific period, save two results and calculate differences in operations and times.

How to read the result

Separate data and log files because their patterns are different. It jointly considers latency, number of operations and volume transferred; a high average with two readings does not carry the same weight as one sustained for millions. The latency observed by SQL Server includes the time that operations wait. Relate files that share a path or volume before attributing the problem to a single base.

What this does establish

Allows you to compare the cumulative latency that SQL Server observed per file and locate paths that require interval metering or infrastructure review.

What this still does not establish

It does not prove that the physical device is the cause, it does not describe percentiles or peaks, and it does not alone separate demand from the base of other storage consumers.

Next reasonable check

Compare differences between captures during the incident and correlate them with waits, load and operating system or platform metrics. Also check file growth and physical distribution.

Search SQL Server