SQL Server I/O stall high: per-file storage latency from dm_io_virtual_file_stats

When PAGEIOLATCH_* or WRITELOG climbs to the top of sys.dm_os_wait_stats, the question is whether storage is the bottleneck or whether SQL Server is doing too much physical I/O because the buffer pool is undersized. sys.dm_io_virtual_file_stats(NULL, NULL) is the only DMV that answers this at file granularity from inside the engine.

The values it returns are cumulative since the SQL Server service last started, so a single snapshot is almost useless. Compute deltas over a short window (15-60 seconds during the incident) and divide stall time by operation count to get current per-file latency. Lifetime averages on a long-running instance hide the bad minutes that matter.

Two patterns diverge here. Elevated read latency on data files is frequently a buffer pool or query-plan problem dressed up as storage. Elevated write latency on transaction log files is almost always storage-bound, and because every commit waits on log flush, log write latency directly bounds transaction throughput.

What this means

sys.dm_io_virtual_file_stats reports four cumulative counters per database file: io_stall_read_ms, num_of_reads, io_stall_write_ms, num_of_writes. Stall is wall-clock time that SQL Server workers spent waiting for I/O completion. To get the current latency for a file over an interval, compute the delta of each counter between two snapshots and divide:

  • avg_read_latency_ms = (io_stall_read_ms_t2 - io_stall_read_ms_t1) / (num_of_reads_t2 - num_of_reads_t1)
  • avg_write_latency_ms = (io_stall_write_ms_t2 - io_stall_write_ms_t1) / (num_of_writes_t2 - num_of_writes_t1)

These thresholds apply to interval-sampled values, not lifetime averages:

File typeExcellentAcceptableDegradedSevere
Data file reads< 10 ms10-20 ms> 20 ms> 50 ms
Log file writes< 2 ms2-5 ms> 5 ms> 15 ms

Treat sustained log write latency above 20 ms as a PAGE-level condition: every write transaction on that database is being throttled by the log disk.

A persistent trap is identifying the log file. Many legacy scripts filter WHERE file_id = 2. That is wrong for databases with multiple data filegroups (tempdb with multiple data files, user databases with secondary data files in additional filegroups). Always join sys.master_files and filter type_desc = 'LOG'.

flowchart TD
    A[High I/O stall on file] --> B{Reads or log writes?}
    B -->|Data reads| C{BCHR and PLE?}
    C -->|BCHR low, PLE dropping| D[Buffer pool pressure, not storage]
    C -->|BCHR normal| E[Storage subsystem or large scan]
    B -->|Log writes| F{Top wait type?}
    F -->|WRITELOG| G[Local log disk bottleneck]
    F -->|HADR_SYNC_COMMIT| H[AG secondary hardening on primary]
    E --> I{OS disk latency also high?}
    I -->|No| J[Filter driver in I/O path]
    I -->|Yes| K[Array, controller, or cloud IOPS cap]

Common causes

CauseWhat it looks likeFirst thing to check
Storage subsystem degradedRead and write latency both spike across all files on the same volume; no buffer pool changeOS disk counters, array events, error 833 in SQL error log
Buffer pool pressureRead latency rises while PLE drops and BCHR falls; writes unaffectedBuffer cache hit ratio, Page Life Expectancy, Memory Grants Pending
Cloud disk IOPS or throughput capLatency spikes when throughput approaches provisioned limit; falls back below the capCloud provider disk metrics for IOPS and MB/s versus provisioned
Filter driver (AV, backup, encryption)SQL Server reports high stall; Perfmon Avg. Disk sec/Transfer is lowfltmc instances on the host; AV exclusions for SQL directories
AG secondary on synchronous commitHADR_SYNC_COMMIT is dominant wait, not WRITELOG; primary log stall looks elevated but local disk is fastSecondary replica I/O stall, send queue, redo queue
Misplaced filesLatency concentrated on one volume; log and data on same spindle or LUNsys.master_files physical_name mapping to volumes
Long sequential maintenance scanRead latency spikes only during index rebuild, CHECKDB, or full backup; recovers afterDefault trace autogrowth events and maintenance job schedule

Quick checks

All read-only. Requires VIEW SERVER STATE (or the more granular VIEW SERVER PERFORMANCE STATE on SQL Server 2022+).

-- 1. Per-file cumulative snapshot with type_desc so log files are correct
SELECT
    DB_NAME(vfs.database_id) AS db_name,
    mf.name AS logical_file,
    mf.type_desc,
    mf.physical_name,
    vfs.io_stall_read_ms, vfs.num_of_reads,
    vfs.io_stall_write_ms, vfs.num_of_writes,
    CASE WHEN vfs.num_of_reads > 0
         THEN vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads ELSE 0 END AS lifetime_avg_read_ms,
    CASE WHEN vfs.num_of_writes > 0
         THEN vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes ELSE 0 END AS lifetime_avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
    ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY lifetime_avg_read_ms + lifetime_avg_write_ms DESC;

-- 2. Delta snapshot helper: run twice, ~30 seconds apart, then compute
--    per-file latency from (delta_stall / delta_ops). Sample 1:
SELECT
    DB_NAME(vfs.database_id) AS db_name, vfs.file_id,
    vfs.io_stall_read_ms, vfs.num_of_reads,
    vfs.io_stall_write_ms, vfs.num_of_writes,
    si.ms_ticks
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
CROSS JOIN sys.dm_os_sys_info si;
-- Rerun in 30s; per-file avg latency = (stall_t2 - stall_t1) / (ops_t2 - ops_t1).

-- 3. Top waits to confirm I/O is actually the bottleneck
SELECT TOP 15 wait_type, waiting_tasks_count, wait_time_ms,
       wait_time_ms - signal_wait_time_ms AS resource_wait_ms,
       signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (
    'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT',
    'CLR_MANUAL_EVENT','LAZYWRITER_SLEEP','SQLTRACE_BUFFER_FLUSH','WAITFOR',
    'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','FT_IFTS_SCHEDULER_IDLE_WAIT',
    'BROKER_EVENTHANDLER','SP_SERVER_DIAGNOSTICS_SLEEP','DIRTY_PAGE_POLL',
    'DISPATCHER_QUEUE_SEMAPHORE','CHECKPOINT_QUEUE','REQUEST_FOR_DEADLOCK_SEARCH',
    'LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE','HADR_WORK_QUEUE','BROKER_TRANSMITTER')
AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

-- 4. Buffer cache hit ratio and PLE (instance-wide; check Buffer Node per NUMA)
SELECT
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Buffer cache hit ratio'
       AND object_name LIKE '%Buffer Manager%') * 1.0 /
    NULLIF((SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Buffer cache hit ratio base'
       AND object_name LIKE '%Buffer Manager%'), 0) * 100.0 AS bchr_pct,
    (SELECT cntr_value FROM sys.dm_os_performance_counters
     WHERE counter_name = 'Page life expectancy'
       AND object_name LIKE '%Buffer Manager%') AS ple_seconds;

-- 5. AG sync-commit penalty: distinguishes local log disk from AG path
SELECT wait_type, waiting_tasks_count, wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('WRITELOG','HADR_SYNC_COMMIT','LOGBUFFER');

-- 6. Error 833 (single I/O request over 15 seconds) in current error log
EXEC sp_readerrorlog 0, 1, '833';

# 7. Windows filter drivers attached to volumes hosting SQL Server files
fltmc instances

How to diagnose it

  1. Take two snapshots of sys.dm_io_virtual_file_stats 30 seconds apart and compute per-file latency deltas. Lifetime averages on an instance running for weeks will mislead you.

  2. Categorize the worst files by type_desc. High latency on data files and high latency on log files have different causes. Treat ROWS and LOG separately.

  3. Cross-check against the top waits. If PAGEIOLATCH_* is dominant, data file read latency is the bottleneck. If WRITELOG is dominant, the log disk is the bottleneck. If HADR_SYNC_COMMIT is dominant, the AG secondary’s I/O path is throttling commits on the primary. The primary’s own log latency may look fine in this case.

  4. Look at buffer pool health. If Buffer cache hit ratio is below 95% on an OLTP workload and PLE is dropping while data read latency is rising, the storage is likely fine and the buffer pool is forcing physical reads. Memory pressure dressed as I/O is the most common false positive.

  5. Compare SQL Server’s reported latency against OS-level disk latency. If Perfmon Avg. Disk sec/Transfer for the same volume is much lower than what dm_io_virtual_file_stats reports, the latency is being added between SQL Server and the partition manager. That is typically a filter driver (antivirus, backup agent, encryption product). Run fltmc instances and confirm SQL Server directories are excluded from AV scanning.

  6. On cloud VMs (Azure, AWS, GCP), check whether latency spikes correlate with hitting the provisioned IOPS or MB/s cap for the disk tier. Cloud disk throttling is sudden and sets a hard latency floor you cannot tune around. Only a disk SKU change helps.

  7. Look for storage-related entries in the SQL Server error log. Error 833 means an individual I/O request took longer than 15 seconds. Error 825 means a read failed and was retried successfully. The underlying medium is deteriorating and a hard 823 or 824 is coming.

Metrics and signals to monitor

SignalWhy it mattersWarning sign
Per-file avg read latency (delta)Engine’s own view of data file storage latencyData reads > 20 ms sustained
Per-file avg write latency (delta) on log filesBounds commit throughput on that databaseLog writes > 5 ms sustained; PAGE > 20 ms
PAGEIOLATCH_* wait timeWorker threads blocked on physical readsRising share of total wait time
WRITELOG wait timeWorker threads blocked on log flushRising share of total wait time
HADR_SYNC_COMMIT wait timeAG secondary hardening delay on primary commitsPresent on synchronous-commit primaries
Buffer cache hit ratioWhether physical reads are storage-driven or memory-drivenOLTP < 95% with rising read latency
Page Life ExpectancyPage eviction rate; falling PLE forces more readsSudden 50%+ drop from baseline
Lazy writer pages/secBuffer pool under active memory pressureNonzero outside checkpoint
Disk space on data, log, TempDB volumesAuto-grow stalls all I/O to that fileBelow 20% free with autogrow headroom uncertain
Error 825 (read retry succeeded)Disk failing before a hard 823 or 824Any non-zero count
sample_ms reliabilityMay wrap on long-running instances <!– TODO: verify whether sample_ms wrapping still occurs on SQL Server 2016+ where column is bigintUse ms_ticks from sys.dm_os_sys_info for time deltas

Fixes

If reads are slow but writes are fine: check the buffer pool first

Before paging storage, confirm the buffer pool is not the cause. Check max server memory against physical RAM and other workloads on the host. Check whether a single query is hoarding a memory grant (visible in sys.dm_exec_query_memory_grants). One bad plan with an oversized sort can evict useful pages and force re-reads. Increase max server memory if it is too low, or tune and kill the grant-hoarding query. Adding storage IOPS will not help if the engine is just issuing more physical reads than necessary.

If log writes are slow and WRITELOG is dominant

The log disk is the bottleneck. Confirm the log file is on its own volume, not shared with data files or TempDB. Check array controller queue depth, HBA saturation, and any snapshot or replication jobs running on the storage layer. Verify the log file is pre-sized to avoid auto-grow events. Log file auto-grow cannot use Instant File Initialization and stalls every log-writing transaction while the new extent is zero-initialized. On cloud VMs, confirm the disk SKU is provisioned for the write IOPS your workload needs. Cloud log disks often need a higher tier than data disks.

If log latency looks high but HADR_SYNC_COMMIT is dominant

The local log disk is fine. The synchronous-commit secondary is taking too long to harden log records. Check the secondary replica’s own dm_io_virtual_file_stats for log write latency, and check the network path between replicas for bandwidth saturation or MTU issues. For emergency relief during an incident you can temporarily switch the AG to asynchronous commit, but that changes your RPO. Only do this with explicit awareness of data-loss exposure.

If the storage layer is genuinely degraded

Pull the array events. Check for RAID rebuild, controller firmware issues, path failover events, and any storage maintenance window. On cloud, check provider status pages and disk-level metrics. If errors 823, 824, or 825 are present in the SQL error log, treat this as a hardware incident, not a tuning exercise. Open a ticket with the storage vendor and verify your most recent backups are restorable.

If SQL Server reports high latency but the OS does not

This is the filter driver case. Run fltmc instances to enumerate filter drivers attached to the volume hosting SQL Server files. Antivirus, backup agents, and file-level encryption products insert themselves into the I/O path. Microsoft’s guidance is to exclude SQL Server data, log, and backup directories from real-time AV scanning. Removing or correctly configuring the offending filter driver is the fix. Do not attempt to tune around it from inside SQL Server.

Prevention

  • Pre-size data and log files for expected growth to avoid auto-grow events. Log file auto-grow is the most expensive growth event in SQL Server and stalls all writes on that database.
  • Monitor dm_io_virtual_file_stats deltas continuously, not reactively. Lifetime averages on a long-uptime instance are noise.
  • Alert on error 825 with the same urgency as 823 and 824. It is the canary for disk failure.
  • Put data, log, and TempDB on separate volumes. Sharing spindles or cloud disk IOPS budgets is a classic misconfiguration.
  • On cloud VMs, size log disk IOPS for peak commit rate, not average. Throttling is sudden and severe.
  • If you use synchronous-commit AG, monitor the secondary’s log write latency as part of primary-side incident response. A slow secondary looks like a slow primary.
  • Apply AV exclusions for SQL Server directories at provisioning time, not after the first incident.

How Netdata helps

  • Per-second per-file read and write latency deltas computed from sys.dm_io_virtual_file_stats, so you see the bad minute instead of the lifetime average.
  • Per-file latency trended alongside PAGEIOLATCH_* and WRITELOG wait time deltas, separating storage-bound stalls from memory-driven reads.
  • Buffer cache hit ratio and PLE correlated with read latency, making the “is it memory or storage?” call faster.
  • HADR_SYNC_COMMIT wait tracking on synchronous-commit primaries, so secondary hardening latency is visible alongside primary log disk latency.
  • Error 833 and error 825 detection surfaced next to I/O stall spikes, so silent disk failures do not stay silent.
  • ML anomaly detection on per-file latency deltas catches subtle drift that absolute thresholds miss on systems with mixed workload profiles.

Netdata’s Microsoft SQL Server monitoring brings these signals together with per-second metrics and ML anomaly detection.