Measure Disk I/O Performance per Database File

calendar Jul 3, 2026 / · 8 min read · sql server sys.dm_io_virtual_file_stats sys.master_files disk io io stall disk latency performance database files dmv monitoring scripts  ·
Share on: linkedin copy

I/O latency is where disk bottlenecks hide. SQL Server accumulates every millisecond of file-level read and write wait in sys.dm_io_virtual_file_stats — one row per database file, reset at each service restart. Joining that DMV to sys.master_files replaces raw file IDs with physical paths and file types, and dividing accumulated stall by operation count converts totals into the average milliseconds per read and per write that a storage or DBA team can act on.

Purpose and Overview

Pinpointing which files carry the most I/O wait is the starting point for any storage performance investigation in SQL Server. When a query stalls waiting for a disk read or write to complete, that wait is recorded against the specific database file where the I/O occurred. Over time those per-file totals expose patterns: a log file with disproportionate write stall points to a VLF or disk-subsystem issue; a data file with high read latency might indicate missing indexes forcing physical reads, or an underpowered storage tier.

sys.dm_io_virtual_file_stats is the DMV that surfaces these per-file I/O statistics. It is a table-valued function accepting a database ID and file ID — passing NULL, NULL for both returns every file across every online database on the instance. The key columns are io_stall_read_ms and io_stall_write_ms, which accumulate the total milliseconds SQL Server waited on reads and writes respectively, paired with num_of_reads and num_of_writes to calculate averages. The io_stall column sums all wait types across both reads and writes, including queued I/O when Resource Governor I/O limits are in effect.

The DMV joins to sys.master_files, a server-scope catalog view that stores one row per database file across the entire instance — data files, log files, and FILESTREAM containers. The join resolves database_id and file_id to the physical file path, logical name, and file type. Together, the two objects produce a report that identifies exactly where disk latency is accumulating and how much throughput each file has handled since the last SQL Server service restart.

Code Breakdown

The query below produces a per-file I/O performance report, ordered by total I/O stall descending so the worst-offending files surface first.

 1SELECT
 2    DB_NAME(fs.database_id)                                    AS database_name,
 3    mf.name                                                    AS logical_name,
 4    mf.physical_name,
 5    mf.type_desc                                               AS file_type,
 6    fs.num_of_reads,
 7    fs.io_stall_read_ms,
 8    CASE fs.num_of_reads
 9        WHEN 0 THEN 0
10        ELSE fs.io_stall_read_ms / fs.num_of_reads
11    END                                                        AS avg_read_ms,
12    fs.num_of_writes,
13    fs.io_stall_write_ms,
14    CASE fs.num_of_writes
15        WHEN 0 THEN 0
16        ELSE fs.io_stall_write_ms / fs.num_of_writes
17    END                                                        AS avg_write_ms,
18    fs.io_stall                                                AS total_io_stall_ms,
19    CAST(fs.num_of_bytes_read    / 1048576.0 AS DECIMAL(18,2)) AS mb_read,
20    CAST(fs.num_of_bytes_written / 1048576.0 AS DECIMAL(18,2)) AS mb_written
21FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
22INNER JOIN sys.master_files AS mf
23    ON  fs.database_id = mf.database_id
24    AND fs.file_id     = mf.file_id
25ORDER BY fs.io_stall DESC;

Calling sys.dm_io_virtual_file_stats

sys.dm_io_virtual_file_stats is a table-valued function, not a view, so it requires explicit parentheses and arguments. Passing NULL for both database_id and file_id returns every online database file on the instance. To scope the result to one database, pass the integer database ID — for example, sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks'), NULL) — which limits output to the files of that database alone. This targeted form is useful when investigating a specific workload rather than surveying all databases at once.

Calculating average read and write latency

The raw io_stall_read_ms and io_stall_write_ms columns are cumulative totals since instance start. Dividing by the matching operation count gives average milliseconds per read or write — the metric that maps directly to storage-tier expectations. A conventional guideline is that average read or write latency above 20–25 ms on a data file signals a storage problem worth investigating; log files are more sensitive because writes are synchronous and sequential, and latency above 10 ms there can stall transactions directly. The CASE ... WHEN 0 THEN 0 guard eliminates a division-by-zero error on files that have seen no reads or writes since the last restart.

Joining to sys.master_files

sys.master_files is a server-scope catalog view — it is not database-scoped, so it does not require a USE statement and can be queried from any database context. It stores the physical path, logical name, and type_desc (ROWS, LOG, or FILESTREAM) for every file across every database on the instance. The join key is the composite (database_id, file_id), which uniquely identifies each file instance-wide. Including physical_name in the output shows the full path on the storage controller, which is essential when a server has multiple drives or mount points and the DBA needs to know which physical spindle or LUN the hot file resides on.

I/O throughput columns

num_of_bytes_read and num_of_bytes_written are cumulative byte counts since instance restart. Converting to megabytes (/ 1048576.0) yields a readable throughput figure that pairs with the latency columns: a file with very high bytes transferred and low stall is working efficiently; high stall relative to throughput signals an I/O subsystem that cannot keep pace with demand. Casting to DECIMAL(18,2) keeps the output readable without excessive decimal places.

Filtering and narrowing results

The ORDER BY fs.io_stall DESC surfaces the worst-offending files first. On a busy production instance with dozens of databases it can help to add WHERE fs.io_stall > 0 to exclude files with no activity, or to filter on mf.type_desc = 'LOG' to examine only transaction logs — the usual first suspects during heavy write workloads. To isolate a single database, add AND DB_NAME(fs.database_id) = N'YourDatabase' rather than changing the function arguments, which keeps the query readable.

Key Benefits and Use Cases

  • Identifies high-latency files — average read and write milliseconds per file point directly to storage tiers or drives that are under-provisioned or overloaded.
  • Separates read from write pressure — io_stall_read_ms vs. io_stall_write_ms distinguishes a read-heavy query workload from a write-heavy one, guiding whether to address missing indexes, increase buffer pool memory, or improve write throughput.
  • Compares data and log files in one result — type_desc lets you split I/O performance between data files and transaction logs without running separate queries.
  • Throughput baseline — cumulative bytes read and written establish a per-file throughput baseline useful before and after storage migrations or hardware upgrades.
  • Instance-wide or scoped — the NULL, NULL argument covers the full instance in one pass; a targeted database ID narrows the scope for focused investigations.
  • No configuration required — the DMV is available across all SQL Server editions and requires no special configuration or trace flags to populate.

Performance Considerations

  • Counters reset on service restart: all values in sys.dm_io_virtual_file_stats accumulate from the last SQL Server service restart, not from a rolling window. A server running for 30 days carries 30 days of totals; a recently restarted instance shows only a short history. Capture snapshots at two points in time and compute the delta to get I/O rates for a specific interval.
  • Buffer pool reads are not physical reads: reads satisfied from the buffer pool do not register as num_of_reads — only physical disk reads do. High num_of_reads on a data file is therefore a direct signal that the working set is not cached, pointing toward a memory-pressure investigation alongside storage.
  • Indirect checkpoint inflates data-file write stall: on databases with indirect checkpoint enabled, the checkpoint process writes dirty pages more aggressively. Elevated io_stall_write_ms on data files may reflect checkpoint activity rather than query writes; cross-reference with checkpoint wait stats before attributing write stall solely to the storage tier.
  • Resource Governor queue time is included in io_stall: if Resource Governor is configured with MAX_IOPS_PER_VOLUME, queued I/O time accumulates in io_stall_queued_read_ms and io_stall_queued_write_ms, and both roll up into io_stall. Unusually high stall on a governed workload may reflect throttling policy, not a hardware bottleneck.
  • Filegroups with multiple files appear as multiple rows: the query reports per-file; a filegroup containing several files returns one row per file. Sum the io_stall and throughput columns grouped by database and type_desc to get a filegroup-level view.

Practical Tips

  • Capture two snapshots several minutes apart and compute the delta to derive current I/O rates rather than lifetime totals; store results in a DBA monitoring table to build a trend baseline over time.
  • Schedule the query as a SQL Server Agent job that writes output to a history table nightly — comparing this week's avg_read_ms and avg_write_ms against last week's baseline catches gradual storage degradation before users report slowness.
  • Correlate high-stall files with sys.dm_exec_query_stats to find the specific queries driving the I/O; a file with sustained high write stall often traces back to a small number of heavy-insert or batch-update statements.
  • Use physical_name to confirm that data and log files for each database reside on separate drives or LUNs — sharing a spindle between data and log is a common misconfiguration this report surfaces immediately.
  • For log files showing high io_stall_write_ms, pair this report with a VLF count query: excessive VLF fragmentation compounds log write latency, and a shrink-and-grow cycle to right-size the log can cut write stall significantly on its own.

Conclusion

sys.dm_io_virtual_file_stats joined to sys.master_files delivers a per-file view of every read stall, write stall, and byte of I/O on the instance since the last service restart. The average latency columns — computed by dividing accumulated stall by operation count — translate raw DMV numbers into actionable storage-tier metrics. This query is the right first diagnostic when slow queries or elevated wait stats point toward disk, and it belongs in any SQL Server monitoring toolkit alongside wait-statistics and memory-pressure checks.

References

Posts in this series