SELECT
LEFT ([mf].[physical_name], 2) AS [DriveLetter],
DB_NAME ([vfs].[database_id]) AS [Database],
[mf].[physical_name] [PhysicalName],
[vfs].[size_on_disk_bytes] / 1024 / 1024 [FileSizeMB],
[vfs].[io_stall],
[vfs].[io_stall_read_ms],
CAST([io_stall_read_ms] / ( 1.0 + [num_of_reads] ) as numeric(10,1)) as 'avg_read_stall_ms',
[vfs].[io_stall_write_ms],
CAST([io_stall_write_ms] / ( 1.0 + [num_of_writes] ) as numeric(10,1)) as 'avg_write_stall_ms',
[io_stall_read_ms] + [io_stall_write_ms] as [io_stalls],
[num_of_reads] + [num_of_writes] as [total_io],
CAST(([io_stall_read_ms]+[io_stall_write_ms]) / ( 1.0 + [num_of_reads] + [num_of_writes]) as numeric(10,1)) as 'avg_io_stall_ms',
[TotalLatency] = CASE
WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0
ELSE [vfs].[io_stall] / ([vfs].[num_of_reads] + [vfs].[num_of_writes])
END,
[ReadLatency] = CASE
WHEN [vfs].[num_of_reads] = 0 THEN 0
ELSE [vfs].[io_stall_read_ms] / [vfs].[num_of_reads]
END,
[WriteLatency] = CASE
WHEN [vfs].[num_of_writes] = 0 THEN 0
ELSE [vfs].[io_stall_write_ms] / [vfs].[num_of_writes]
END,
[vfs].[num_of_reads] [NumOfReads],
[vfs].[num_of_bytes_read] [NumOfBytesRead],
[AvgBytesPerRead] = CASE
WHEN [vfs].[num_of_reads] = 0 THEN 0
ELSE [vfs].[num_of_bytes_read] / [vfs].[num_of_reads]
END,
[vfs].[num_of_writes] [NumOfWrites],
[vfs].[num_of_bytes_written] [NumOfBytesWritten],
[AvgBytesPerWrite] = CASE
WHEN [vfs].[io_stall_write_ms] = 0 THEN 0
ELSE [vfs].[num_of_bytes_written] / [vfs].[num_of_writes]
END,
[AvgBytedPerIO] = CASE
WHEN [vfs].[num_of_reads] = 0 AND [vfs].[num_of_writes] = 0 THEN 0
ELSE ([vfs].[num_of_bytes_read] + [vfs].[num_of_bytes_written]) / ([vfs].[num_of_reads] + [vfs].[num_of_writes])
END
FROM [sys].[dm_io_virtual_file_stats] (NULL, NULL) [vfs]
INNER JOIN [sys].[master_files] [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [TotalLatency] DESC
GO
