Hi All
in
the ms white paper on Waits & Queues there is a query to determine avg i.o stalls
If
Im running this over a single disc box is there any "ballpark figures"
for average amount of stalls per read and write I should be aware of ?
many thanks
simon
--avg stalls per read/write
select
[name],
database_id,
filetype = case sys.database_files.type
when 1 then 'Log'
when 0 then 'Data'
end,
io_stall_read_ms,
num_of_reads,
cast (io_stall_read_ms/(1.0+num_of_reads) as numeric (10,1)) as 'avg_read_stall_ms',
io_stall_write_ms,
num_of_writes,
cast (io_stall_write_ms/(1.0+num_of_writes) as numeric (10,1)) as 'avg_write_stall_ms',
io_stall_write_ms+num_of_writes 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'
from sys.dm_io_virtual_file_stats (11,FILE_IDEX(11))
join sys.database_files
on sys.dm_io_virtual_file_stats .file_id = sys.database_files.file_id
order by avg_io_stall_ms desc
go