December 28, 2012 at 10:36 am
Hey Folks,
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.
\
SELECT
sample_ms,
DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
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 ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
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
Thanks
Greg Jackson
Gregory A Jackson MBA, CSM
December 28, 2012 at 10:49 am
The SAN numbers are reporting just the time in the SAN. The numbers from SQL Server report the time it left SQL Server until it gets back to SQL Server. So the difference here is the time outside of SQL Server and outside of SAN. This can be the OS but is more likely the connection between the server and the SAN.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 28, 2012 at 10:54 am
Holy Moses.
so obvious when you stated it.
Thank you very much......
GAJ
Gregory A Jackson MBA, CSM
December 28, 2012 at 10:57 am
You are welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply