sys.dm_io_virtual_file_stats - Usage & Examples

sys.dm_io_virtual_file_stats exposes internal I/O counters maintained by the SQL Server Storage Engine at the file level. When the Buffer Manager issues read requests to the operating system, the Storage Engine increments num_of_reads and adds the elapsed time to io_stall_read_ms. Write operations follow the same pattern through num_of_writes and io_stall_write_ms.

The DMV accumulates statistics from Windows performance counters and internal SQL Server tracking mechanisms. Each database file maintains separate counters in memory structures that persist until service restart. The io_stall values represent total wait time in milliseconds, not individual operation latency, making average calculations essential for meaningful analysis.

Key behavioral differences exist across versions. SQL Server 2016 introduced size_on_disk_bytes to distinguish allocated space from actual disk consumption for sparse files and snapshots. SQL Server 2019 added more granular tracking for log files under AlwaysOn Availability Groups. SQL Server 2022 improved accuracy for files on cloud storage by accounting for network-induced latency separately from local I/O operations.

The num_of_bytes_read and num_of_bytes_written counters track actual data transfer, not logical page operations. A single 64KB read from a data file appears as 65536 bytes even if only one 8KB page contained relevant data. This distinction becomes critical when analyzing compression effectiveness or large object storage patterns.

-- Identify files with highest average read latency
SELECT 
    DB_NAME(database_id) AS database_name,
    file_id,
    io_stall_read_ms,
    num_of_reads,
    CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10,1)) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads > 0
ORDER BY avg_read_stall_ms DESC;

-- Calculate I/O pressure by database with MB/sec throughput
SELECT 
    DB_NAME(database_id) AS database_name,
    SUM(num_of_reads + num_of_writes) AS total_io_operations,
    SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS total_mb_transferred,
    SUM(io_stall) AS total_io_stall_ms,
    CAST(SUM(io_stall) / (1.0 + SUM(num_of_reads + num_of_writes)) AS NUMERIC(10,1)) AS avg_io_stall_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
GROUP BY database_id
ORDER BY total_io_stall_ms DESC;

-- Identify write-heavy files for log file analysis
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    mf.type_desc,
    vfs.num_of_writes,
    vfs.num_of_bytes_written / 1048576 AS mb_written,
    CAST(vfs.io_stall_write_ms / (1.0 + vfs.num_of_writes) AS NUMERIC(10,1)) AS avg_write_stall_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
WHERE vfs.num_of_writes > 0
ORDER BY vfs.num_of_writes DESC;

-- Compare current I/O patterns to baseline (requires two snapshots)
WITH IoSnapshot AS (
    SELECT 
        database_id,
        file_id,
        num_of_reads,
        num_of_writes,
        io_stall,
        GETDATE() AS snapshot_time
    FROM sys.dm_io_virtual_file_stats(NULL, NULL)
)
SELECT 
    DB_NAME(database_id) AS database_name,
    file_id,
    num_of_reads,
    num_of_writes,
    io_stall,
    snapshot_time
FROM IoSnapshot;
-- Run this query twice with 5-minute interval, then calculate deltas

-- Detect files with disproportionate stall time indicating storage issues
SELECT 
    DB_NAME(database_id) AS database_name,
    file_id,
    num_of_reads + num_of_writes AS total_operations,
    io_stall AS total_stall_ms,
    CAST(io_stall * 100.0 / SUM(io_stall) OVER() AS NUMERIC(5,2)) AS pct_of_total_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
WHERE num_of_reads + num_of_writes > 0
    AND io_stall > 0
ORDER BY pct_of_total_stall DESC;

Reset I/O statistics baseline after maintenance

-- Clear DMV counters by restarting SQL Server service
-- WARNING: This clears ALL DMV statistics, not just I/O stats
-- Only perform during planned maintenance windows
-- DBCC SQLPERF('sys.dm_io_virtual_file_stats', CLEAR); -- Not available
-- Use: Restart SQL Server service to reset cumulative counters
PRINT 'I/O statistics reset requires SQL Server service restart';
PRINT 'Plan this during next maintenance window';

Move high-I/O files to faster storage

-- Script to generate ALTER DATABASE commands for file relocation
-- TEST IN DEVELOPMENT FIRST - Requires downtime
SELECT 
    'ALTER DATABASE ' + QUOTENAME(DB_NAME(vfs.database_id)) + 
    ' MODIFY FILE (NAME = ''' + mf.name + ''', FILENAME = ''F:\FastDrive\' + mf.name + 
    CASE WHEN mf.type = 0 THEN '.mdf' ELSE '.ldf' END + ''');' AS move_command
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
WHERE CAST(vfs.io_stall / (1.0 + vfs.num_of_reads + vfs.num_of_writes) AS NUMERIC(10,1)) > 15
ORDER BY vfs.io_stall DESC;
-- Execute commands during maintenance window with database offline

Add additional data files to distribute I/O load

-- Generate commands to add proportionally-sized data files
-- Reduces I/O contention on single file databases
SELECT 
    'ALTER DATABASE ' + QUOTENAME(DB_NAME(database_id)) + 
    ' ADD FILE (NAME = ''' + DB_NAME(database_id) + '_Data2'', ' +
    'FILENAME = ''G:\Data\' + DB_NAME(database_id) + '_Data2.ndf'', ' +
    'SIZE = ' + CAST(size * 8 / 1024 AS VARCHAR(10)) + 'MB);' AS add_file_command
FROM sys.master_files
WHERE type = 0 -- Data files only
    AND database_id IN (
        SELECT database_id 
        FROM sys.dm_io_virtual_file_stats(NULL, NULL)
        WHERE num_of_reads + num_of_writes > 100000
        GROUP BY database_id 
        HAVING COUNT(*) = 1 -- Single data file databases
    );

Configure instant file initialization for faster growth

-- Check if instant file initialization is enabled
-- Reduces I/O stall during auto-growth events
SELECT 
    servicename,
    instant_file_initialization_enabled
FROM sys.dm_server_services
WHERE servicename LIKE 'SQL Server%';

-- If disabled, grant "Perform volume maintenance tasks" to SQL Server service account
-- Requires Windows administrator privileges, not T-SQL configurable
PRINT 'Grant "Perform volume maintenance tasks" privilege to SQL Server service account';
PRINT 'This eliminates zero-initialization overhead during file growth';

Configure database files with appropriate initial sizes and growth increments to minimize auto-growth events. Set data file growth to fixed MB values (not percentages) and limit growth to prevent runaway expansion. Enable instant file initialization on the SQL Server service account to eliminate zero-initialization overhead.

Implement proactive monitoring of average I/O latency thresholds. Data file reads exceeding 15ms and log writes exceeding 5ms indicate storage performance issues requiring investigation. Create baselines during low-activity periods and alert on sustained deviations above 150% of baseline values.

Separate data and log files across different physical storage devices when possible. Place transaction logs on dedicated storage with write-optimized characteristics. For databases with heavy read workloads, consider multiple data files distributed across storage devices to parallelize I/O operations.

Use tempdb best practices with multiple equally-sized data files (typically one per CPU core up to 8 files) to reduce allocation contention. Monitor sys.dm_io_virtual_file_stats for tempdb specifically as high I/O values often indicate inefficient query plans or insufficient memory allocation.

Schedule regular analysis of I/O patterns to identify trending problems before they impact performance. Databases showing consistent growth in io_stall values relative to operation counts indicate degrading storage performance requiring proactive intervention.