3

I'm trying to use the sys.dm_io_virtual_file_stats DMF to generate so IO wait information across a number of my servers. I came across this post by Paul Randall: http://www.sqlskills.com/blogs/paul/survey-tempdb-and-transaction-log-io-latencies/. In it he asked people to respond with the results of this query:

SELECT
    [database_id],
    [file_id],
    [ReadLatency] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END
FROM
    sys.dm_io_virtual_file_stats (NULL, NULL)
WHERE
    [file_id] = 2 OR [database_id] = 2;
GO

My question is why is he using io_stall_read_ms / num_of_reads instead of io_stall_read_ms / num_of_bytes_read in order to get the average. I would think ms/byte would be more informative than ms/read. Which at least in part leads me to believe I don't understand what the num_of_reads/writes columns actually represent.

BOL just says

Number of reads issued on the file.

which doesn't really help me much. I'd appreciate any clarification I can get.

3
  • 1
    Typically, SQL Server does I/O in 64KByte chunks. Reads/sec is a measure of the number of IO operations per second which is more useful than the strict number of bytes per second.
    – Hannah Vernon
    Commented Jan 23, 2015 at 17:36
  • 2
    Latency per read operation seems much more relevant to me than latency per byte. You want to know how long the disk is taking to respond; latency per byte could be all over the place due to different types of data (think LOBs, XML, FileStream, int columns, compressed data, columnstore, etc.), and different efficiencies built into reading all those different types of data. I'm not sure what latency / byte would really tell you. Commented Jan 23, 2015 at 17:36
  • Thanks. That helps. I was thinking backwards that latency / byte was more relevant. Commented Jan 23, 2015 at 17:45

1 Answer 1

4

The latency of of the I/O system (which the average latency per IOPS is an indicator of) tell you if you are adding more load on the I/O system than it can handle. Because of this, the average latency, when compared with the media you are running on provides a good indicator as to whether I/O is the problem or not.

As a rule of thumb, here are the latencies you should expect if you are not overloaded on the I/O system

  • Spinning media: Around 5-10ms
  • SSD: <1ms

If you are seeing latencies higher than that, you know that the I/O system is being asked to do more work than it can handle

3
  • So you are saying I should look into the instance with the average wait of ~5k ms for tempdb? :) Commented Jan 26, 2015 at 15:21
  • That would be a good start... 5 seconds for an I/O is eternity and then some. Let me guess: You have a SAN? Commented Jan 26, 2015 at 17:21
  • Fortunately that particular instance isn't one I support but I assume so. It's the worst on a list of 170 prod instances. Our read waits are all pretty good but some of the write waits are real doosies. Commented Jan 26, 2015 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.