patternsqlMinor
Confused about sys.dm_io_virtual_file_stats num_of_reads/writes column
Viewed 0 times
num_of_readswritescolumnconfuseddm_io_virtual_file_statssysabout
Problem
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:
My question is why is he using
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.
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;
GOMy 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.
Solution
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
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
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:
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
Context
StackExchange Database Administrators Q#90031, answer score: 4
Revisions (0)
No revisions yet.