SELECT
DB_NAME(a.database_id) AS [Database Name]
--,a.FILE_ID
,i.name
,a.io_stall_read_ms
,a.num_of_reads
,CAST(a.io_stall_read_ms / (1.0 + a.num_of_reads) AS NUMERIC(10 ,1)) AS [avg_read_stall_ms]
,a.io_stall_write_ms
,a.num_of_writes
,CAST(
a.io_stall_write_ms / (1.0 + a.num_of_writes) AS NUMERIC(10 ,1)
) AS [a.avg_write_stall_ms]
,a.io_stall_read_ms + a.io_stall_write_ms AS [io_stalls]
,a.num_of_reads + a.num_of_writes AS [total_io]
,CAST(
(a.io_stall_read_ms + a.io_stall_write_ms) / (1.0 + a.num_of_reads + a.num_of_writes) AS NUMERIC(10 ,1)
) AS [avg_io_stall_ms],
i.physical_name
FROM
sys.dm_io_virtual_file_stats(NULL ,NULL)a
INNER JOIN
sys.master_files i
ON a.file_id = i.file_id
AND a.database_id = i.database_id
--WHERE i.database_id in ('7')
ORDER BY
avg_io_stall_ms DESC;
Similar Posts: