Latency vs Latency ????

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Hardware » Latency vs Latency ????

Latency vs Latency ????

Rate Topic

Display Mode

Topic Options

Author

Message

GregoryAJackson

Posted Friday, December 28, 2012 10:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

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

Post #1400976

SQLRNNR

Posted Friday, December 28, 2012 10:49 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

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 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1400977

GregoryAJackson

Posted Friday, December 28, 2012 10:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 26, 2013 2:11 PM
Points: 108, Visits: 485

Holy Moses.
so obvious when you stated it.

Thank you very much......

GAJ

Gregory A Jackson MBA, CSM

Post #1400980

SQLRNNR

Posted Friday, December 28, 2012 10:57 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 5:03 PM
Points: 18,853, Visits: 12,438

You are welcome.

Post #1400985

« Prev Topic | Next Topic »

Permissions