Find Disk IO Statistics DMV

This gem of a query has been indispensable in determining IO latency for our servers. 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 (I believe) 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.

Thanks to my partner Chris for providing this.

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) AS NUMERIC(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;

2 comments
Mike W. 16 Jul 2013 at 12:23 pm

Is it possible for you to post a quick explanation of what the results of this query mean? For example, what exactly does the avg_is_stall_ms column tell us? Is there a specific number or threshold for this column where we should become concerned with disk latency? Thanks.

Mike W. 15 Jul 2013 at 1:59 pm

…would it be possible for you to post a short synopsis of how to interpret the results of this query? Some of the column names used are intuitive and self-explanatory but what are the significance of the numbers returned? For example, what is the threshold for when the avg_io_stall_ms value is “too high”? Thanks!

Featured Articles

 Site Author

  • Thanks for visiting!