Database Reference
In-Depth Information
-- Set test interval (1 hour)
waitfor delay '00:01:00.000';
;with Stats(db_id, file_id, Reads, ReadBytes, Writes
,WrittenBytes, ReadStall, WriteStall)
as
(
select
s.database_id, s.file_id
,fs.num_of_reads - s.num_of_reads
,fs.num_of_bytes_read - s.num_of_bytes_read
,fs.num_of_writes - s.num_of_writes
,fs.num_of_bytes_written - s.num_of_bytes_written
,fs.io_stall_read_ms - s.io_stall_read_ms
,fs.io_stall_write_ms - s.io_stall_write_ms
from
#Snapshot s cross apply
sys.dm_io_virtual_file_stats(s.database_id, s.file_id) fs
)
select
s.db_id as [DB ID], d.name as [Database]
,mf.name as [File Name], mf.physical_name as [File Path]
,mf.type_desc as [Type], s.Reads
,convert(decimal(12,3), s.ReadBytes / 1048576.) as [Read MB]
,convert(decimal(12,3), s.WrittenBytes / 1048576.) as [Written MB]
,s.Writes, s.Reads + s.Writes as [IO Count]
,convert(decimal(5,2),100.0 * s.ReadBytes /
(s.ReadBytes + s.WrittenBytes)) as [Read %]
,convert(decimal(5,2),100.0 * s.WrittenBytes /
(s.ReadBytes + s.WrittenBytes)) as [Write %]
,s.ReadStall as [Read Stall]
,s.WriteStall as [Write Stall]
,case when s.Reads = 0
then 0.000
else convert(decimal(12,3),1.0 * s.ReadStall / s.Reads)
end as [Avg Read Stall]
,case when s.Writes = 0
then 0.000
else convert(decimal(12,3),1.0 * s.WriteStall / s.Writes)
end as [Avg Write Stall]
from
Stats s join sys.master_files mf with (nolock) on
s.db_id = mf.database_id and
s.file_id = mf.file_id
join sys.databases d with (nolock) on
s.db_id = d.database_id
where
s.Reads + s.Writes > 0
order by
s.db_id, s.file_id
option (recompile)
