sys.dm_io_virtual_file_stats
use YourDBName select vfs.database_id, db_name(vfs.database_id) database_name, fs.file_id, fs.name, fs.type_desc, fs.physical_name, fs.size, vfs.sample_ms, vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms, vfs.io_stall_read_ms * 1.0 / vfs.num_of_reads * 1.0 as io_stall_average_read_ms, vfs.io_stall_queued_read_ms, vfs.num_of_writes, vfs.num_of_bytes_written, vfs.io_stall_write_ms, vfs.io_stall_write_ms * 1.0 / vfs.num_of_writes * 1.0 as io_stall_average_write_ms, vfs.io_stall_queued_write_ms, vfs.io_stall, vfs.size_on_disk_bytes, vfs.file_handle from sys.dm_io_virtual_file_stats(db_id(), null) as vfs inner join sys.database_files as fs on vfs.file_id = fs.file_id where vfs.num_of_reads > 0 or vfs.num_of_writes > 0 order by type_desc, io_stall_average_read_ms desc;
Range | Latency ---------------|---------------- Excellent | < 1ms Very good | < 5ms Good | 5ms – 10ms Poor | 10ms – 20ms Bad | 20ms – 100ms Shockingly bad | 100ms – 500ms WOW! | > 500ms