select
db_name(vfs.database_id) as database_name,
--vfs.file_id,
mf.name as logical_file_name,
--vfs.sample_ms,
vfs.num_of_reads,
--vfs.num_of_bytes_read,
--vfs.io_stall_read_ms,
case when (vfs.num_of_reads = 0) then null else (vfs.num_of_bytes_read / vfs.num_of_reads) end as avg_bytes_per_read,
case when (vfs.num_of_reads = 0) then null else (vfs.io_stall_read_ms / vfs.num_of_reads) end as avg_stall_per_read,
case when (vfs.num_of_bytes_read / 1048576 = 0) then null else (vfs.io_stall_read_ms / (vfs.num_of_bytes_read / 1048576)) end as avg_stall_per_MB_read,
vfs.num_of_writes,
--vfs.num_of_bytes_written,
--vfs.io_stall_write_ms,
case when (vfs.num_of_writes = 0) then null else (num_of_bytes_written / vfs.num_of_writes) end as avg_bytes_per_write,
case when (vfs.num_of_writes = 0) then null else (io_stall_write_ms / vfs.num_of_writes) end as avg_stall_per_write,
case when (vfs.num_of_bytes_written / 1048576 = 0) then null else (vfs.io_stall_write_ms / (vfs.num_of_bytes_written / 1048576)) end as avg_stall_per_MB_written,
case when (vfs.num_of_writes = 0) then null else (vfs.num_of_reads / vfs.num_of_writes) end as reads_to_writes_ratio,
case when (vfs.num_of_bytes_written = 0) then null else (vfs.num_of_bytes_read / vfs.num_of_bytes_written) end as read_bytes_to_written_bytes_ratio
--vfs.io_stall, -- = io_stall_read_ms + io_stall_write_ms.
--vfs.size_on_disk_bytes,
--vfs.file_handle
from
sys.dm_io_virtual_file_stats(null,null) vfs
left join sys.master_files mf on mf.database_id = vfs.database_id
and mf.file_id = vfs.file_id
order by
vfs.database_id,
vfs.file_id