A DMV a Day – Day 13

The DMV for Day 13 is sys.dm_io_virtual_file_stats, which is described by BOL as:

Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.

This DMV has two parameters, which are database_id and file_id. You can specify NULL for either parameter, in which case, information on all of the databases and/or all of the files will be returned.  This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Calculates average stalls per read, per write, and per total input/output for each database file. 
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],io_stall_write_ms, 
num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],
io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],
CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) 
AS [avg_io_stall_ms]
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;

This query allows you to see the number of reads and writes on each data and log file for every database running on the instance. It is sorted by average io stall time in milliseconds. This allows you to see which files are waiting the most time for disk I/O. It can help you to decide where to locate individual files based on the disk resources you have available. You can also use it to help persuade someone like a SAN engineer that SQL Server is seeing disk bottlenecks for certain files.