We all are aware of sys.dm_io_virtual_file_stats and how useful is this DMV, But to be honest I could not find even one place which could give me the right working script leveraging this DMV and tell me if currently IO is an issue or not!
Well it think wait is over.I tried to put something together and come up with this. Feel free to correct this, but this works for me.
Anything more than 25 ms(that’s a Microsoft threshold J), practically anything more than 80 - 100ms can be something which you can dig into.
This gives you the real time picture and not since last time sql server started.
You can change the WAITFOR DELAY number to increase the delta figure.
-- Calculates average
stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(database_id) AS [Database Name], file_id ,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_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]
into
#iostats_first
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;
WAITFOR DELAY '00:05:00';
-- Calculates average
stalls per read, per write, and per total input/output for each database file.
SELECT DB_NAME(database_id) AS [Database Name], file_id ,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_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]
into
#iostats_second
FROM sys.dm_io_virtual_file_stats(null,null)
ORDER BY avg_io_stall_ms DESC;
select f.[Database Name],f.file_id,
(s.io_stall_read_ms - f.io_stall_read_ms) as 'io_stall_read_ms',
case (s.io_stall_read_ms - f.io_stall_read_ms)
when 0 then 0
else
((s.io_stall_read_ms - f.io_stall_read_ms)/(s.num_of_reads - f.num_of_reads))
end as 'avg_read_stall_ms',
(s.io_stall_write_ms -
f.io_stall_write_ms)
as 'io_stall_write_ms',
case (s.io_stall_write_ms -
f.io_stall_write_ms)
when 0 then 0
else
((s.io_stall_write_ms -
f.io_stall_write_ms)/(s.num_of_writes - f.num_of_writes))
end as 'avg_write_stall_ms',
(s.io_stalls - f.io_stalls) as 'io_stalls',
case (s.io_stalls - f.io_stalls)
when 0 then 0
else((s.io_stalls - f.io_stalls)/(s.total_io - f.total_io)) end as 'avg_io_stall_ms'
from #iostats_first
f
join
#iostats_second s on f.[Database
Name] = s.[Database
Name]
and f.file_id = s.file_id
drop table #iostats_first
drop table #iostats_second
