Problem Statement: Script to get the virtual file waits for IO waits.
Solution: Virtual File waits are useful in identifying the IO waits occurring on any of the database file. This script is used along with SQL server wait stats script to understand the IO waits well.
SELECT Db_name(vfs.database_id) AS database_name,
vfs.database_id,
vfs.FILE_ID,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency,
io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency,
io_stall / NULLIF(num_of_reads + num_of_writes, 0) AS avg_total_latency,
num_of_bytes_read / NULLIF(num_of_reads, 0) AS avg_bytes_per_read,
num_of_bytes_written / NULLIF(num_of_writes, 0) AS avg_bytes_per_write
,
vfs.io_stall,
vfs.num_of_reads,
vfs.num_of_bytes_read,
vfs.io_stall_read_ms,
vfs.num_of_writes,
vfs.num_of_bytes_written,
vfs.io_stall_write_ms,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes
,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC
Good info Pramod!
More to come Hemant.
Stay tuned…