SELECT
DB_NAME(vfs.database_id) AS [Database],
mf.physical_name AS [File Path],
mf.type_desc AS [File Type], — DATA or LOG
vfs.num_of_reads,
vfs.io_stall_read_ms,
CASE WHEN vfs.num_of_reads = 0 THEN 0
ELSE (vfs.io_stall_read_ms / vfs.num_of_reads) END AS [Avg Read Latency (ms)],
vfs.num_of_writes,
vfs.io_stall_write_ms,
CASE WHEN vfs.num_of_writes = 0 THEN 0
ELSE (vfs.io_stall_write_ms / vfs.num_of_writes) END AS [Avg Write Latency (ms)],
vfs.num_of_reads + vfs.num_of_writes AS [Total IOs],
vfs.io_stall,
vfs.size_on_disk_bytes / 1024 / 1024 AS [File Size MB]
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf
ON vfs.database_id = mf.database_id
AND vfs.file_id = mf.file_id
ORDER BY [Avg Read Latency (ms)] DESC, [Avg Write Latency (ms)] DESC;