MAX IO Stall and the IO By File drill down are two separate collections.
A. The Max IO value in Spotlight Homepage is from SQLIOStall procedure which calls our script QS_IOStall.sql . You can review the script here C:\Program Files\Quest Software\Diagnostic Server\Agent\conf\Package\sqlserver_spotlight\2005\QS_IOStall.sql
use tempdb
select (mf.database_id * 100000) + mf.file_id as N'key',db_name(mf.database_id) as N'databasename', rtrim(mf.physical_name) as N'filename',
io_stall as N'iostall',
num_of_writes + num_of_reads as N'iocount'
from sys.dm_io_virtual_file_stats(null,null) as divfs
join sys.master_files as mf
on mf.database_id = divfs.database_id
and mf.file_id = divfs.file_id
The general algorithm is the following:
(Max IO Stall Time - SQLIOStall procedure):
- Running QS_IOStall.sql script
- WaitPerIO = positiveDelta(SQLIOStall.iostall) / positiveDelta (SQLIOStall.iocountrate) // iostall=IOWaitMS , iocountrate=NumberReads+NumberWrites
B. The I/O by File drill-down values are from VirtualFileStats procedure.
The general algorithm is the following:
(IO drilld down (IO by File) - VirtualFileStats procedure):
- Running the QS_VirtualFileStats.sql script
- WaitPerIO = positiveDelta(VirtualFilestats.IOWaitMS)/(positiveDelta(VirtualFileStats.NumberReads) + positiveDelta(VirtualFileStats.NumberWrites))
