SELECT db_name(database_id) AS DBName, FILE_ID, -- File ID 2 = Log, File Id = 1 Data CASE WHEN num_of_reads = 0 THEN 0 ELSE io_stall_read_ms/num_of_reads END AS AvgIOStallRead_ms, CASE WHEN num_of_writes = 0 THEN 0 ELSE io_stall_write_ms/num_of_writes END AS AvgIOStallWrites_ms FROM sys.dm_io_virtual_file_stats(null,null)
You can use this DMV to see what SQL Server thinks about the I/O stall times. I don’t rely on it 100% but it helpful to see which file is generating the most I/O or is performing the worst. However if you have Avg IO Stall time for read or write over 100 ms, I think it requires investigation. Correlate these with the Disk KPIs such as Avg Disk Sec/Read and Avg. Disk Sec/Write.
References: #9, #10, #11