Measure SQL Server I/O Latency With Interval Samples

A file showing a lifetime average read latency of 4 milliseconds can still have a serious storage problem during the current incident. Conversely, an old slow maintenance window can keep a lifetime average high long after the problem disappears. SQL Server file I/O counters are cumulative, so a single snapshot rarely answers "what is happening now?"

Measure a defined interval and relate it to the workload that was active during that interval. Keep data-file reads, data-file writes, and log writes separate. They represent different access patterns and can have very different consequences for application response time.

Calculate deltas, then divide

The following read-only diagnostic captures two samples five seconds apart. It creates only a local temporary table. Run it from a connection with the diagnostic permissions required by your SQL Server 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 divides the change in read-stall milliseconds by the change in completed reads. AverageWriteMs does the same for writes. The multiplication by 1.0 prevents integer division. A NULL result means no corresponding operations were observed, not zero-millisecond storage.

Read the operation count beside the average. A 100-millisecond average based on one read is a different observation from the same average across thousands of reads. Five seconds is a compact demonstration, not a universally representative interval. Collect repeated samples covering the user-visible slowdown and a comparable normal period.

The filters reject obvious counter decreases and nonpositive sampling intervals. They do not make comparisons across restarts, file replacement, or database lifecycle changes trustworthy. Record instance start time and file identity in a durable collector, and discard intervals that cross those boundaries. Do not reset shared diagnostic counters merely to simplify a calculation.

Averages also hide distribution. Many fast operations and a few very slow ones can share the same average as uniformly mediocre storage. File-level counters cannot reveal a precise percentile or identify the exact query responsible for every stall.

Match latency to workload symptoms

Slow data reads may accompany PAGEIOLATCH waits, but excessive physical reads can originate in query design or insufficient cache residency. A query scanning far more data than necessary can stress otherwise healthy storage. Check its access path and read volume before deciding that faster disks are the only fix.

Slow log writes can lengthen commit time and appear alongside WRITELOG waits. Small, frequent transactions may be sensitive to write latency even when throughput is low. Data-file write latency has a different relationship to checkpoints and background flushing. Do not average all these paths into one storage score.

Add operation rate, bytes transferred, and application timing to the investigation. A saturated throughput limit can look different from a low-IOPS workload waiting on individual writes. Correlate the same time window with operating-system and storage-platform measurements rather than comparing unrelated daily averages.

Also distinguish PAGEIOLATCH, which involves I/O, from PAGELATCH, which represents an in-memory latch. Similar names do not imply the same cause. Buying storage does not resolve a hot in-memory allocation page.

Use the evidence to choose the next change

Identify which files and workloads changed at the incident boundary. A newly overlapping backup, index operation, bulk load, or report can create contention that was absent during the baseline. Shared virtual infrastructure can also impose limits outside the database engine.

Avoid declaring a universal acceptable millisecond threshold. The service requirement, operation size, queueing, storage architecture, and log durability path all matter. Compare the affected workload with its own healthy baseline and the platform's expected capabilities.

If a query rewrite reduces reads, rerun the same report and observe both latency and total I/O work. If storage configuration changes, validate application duration and commit behavior as well as the file average. A lower number in one DMV is useful only when it explains an improvement users can experience.

Preserve the samples, timestamps, workload context, and chosen action. This creates a repeatable diagnosis for the next incident and prevents an old cumulative average from becoming a permanent, unsupported verdict about the storage system.

Technical references: Microsoft Learn: File I/O statistics · Microsoft Learn: Troubleshoot slow I/O.