Find Disk IO Statistics DMV

The benefit of this is not having to rely on perfmon to interpret the latency (which can be a crapshoot). Here, SQL itself using SQL OS to measure latency and usage statistics. In the past, perfmon has been sketchy in monitoring physical disk stats because it is not reliable when measuring a SAN. The old trick was to make sure perfmon’s refresh interval was below 2 second refresh in order to get good statistics. Now with this, we can see the exact latency of read / writes against the actual files.

SELECT  
    DB_NAME(fs.database_id) AS [DATABASE Name], 
    mf.physical_name, 
    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) ASNUMERIC(10,1)) AS [avg_io_stall_ms]
 FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS fs
 INNER JOIN sys.master_files AS mf
    ON fs.database_id = mf.database_id
    AND fs.[file_id] = mf.[file_id]
 ORDER BY avg_io_stall_ms DESC;