Latency vs Latency ????

  • 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

  • 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

  • Holy Moses.

    so obvious when you stated it.

    Thank you very much......

    GAJ

    Gregory A Jackson MBA, CSM

  • 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