I'm a bit confused regarding the Latency numbers reported by SQL Server Virtual File Stats (sys.dm_io_virtual_file_stats) compared to the Latency reported by the SAN Itself.
According to my Systems Engineers, the SAN is only reporting "avg read\write latency" of ~20 whereas teh Virtual File Stats Read Latency of 93 and Write Latency of 87.
How are we getting such a vastly different number and how can I get an apples to apples comparison?
the query I'm using is below (the Systems Engineers are using the Tools Built into the SAN to get their figures). The SAN is a NetApp 2040.
DB_NAME(vfs.database_id) AS database_name ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
where vfs.database_id =5
ORDER BY avg_total_latency DESC
Gregory A Jackson MBA, CSM