1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

select convert(varchar(19), getdate(), 121) as date_time
	 , db_name(df.database_id) as DatabaseName
	 , f.name as FileLogicalName
	 , f.filename
	 , case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end as 'ms/io'
	 , case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end  as 'ms/Read io'
	 , case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end  as 'ms/Write io'
  from sys.dm_io_virtual_file_stats(null,null) as df
  join sys.sysaltfiles as f
    on f.dbid= df.database_id
        and f.fileid = df.file_id
 --where df.database_id in(DB_ID('msdb'), DB_ID('TempDB'))
 where case when num_of_reads+num_of_writes>0 then io_stall/(num_of_reads+num_of_writes) else 0 end >50
	or case when num_of_reads>0 then io_stall_read_ms/num_of_reads else 0 end >50
	or case when num_of_writes>0 then io_stall_write_ms/num_of_writes else 0 end >50
 order by 5 desc