THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Disk Queue Length: Some Data Points may Help!

There has been much discussion on the usefulness of disk queue length as an indicator of a disk I/O bottleneck. Bob Dorr, for instance, addressed this issue directly in his excellent blog, SQL Server Urban Legends Discussed. But the issue is not settled for many since we still frequently see people using disk queue length as the key measure in analyzing disk I/O traffic, and still frequently get disk queue length related questions.

Hopefully, some concrete empirical data should help add clarity to the point that disk queue length > 2 may not indicate a problem and there are more useful disk I/O counters (e.g. I/O latency and I/O block size).

The following chart shows the data points taken on a real disk I/O path when 8K random reads are applied. The X-axis units are the disk queue length. The Y axis on the left represents I/Os per second, and the Y axis on the right represents I/O latency.

Clearly, there is no disk I/O bottleneck when the queue length is at 2, 8, 16, or arguably even 32. Why? Because when the queue length is controlled at 2, 8, or 16, the I/O latency is relatively small (< 10ms) and because the I/O path still has room to handle higher load to achieve higher I/Os per second.

Perhaps, that's because there are 8 or more spindles behind the I/O path. Maybe. But the beauty of using the I/O latency counter together with the I/O block size measure is that you don't really have to know how many spindles are there behind the scene in order to accurately assess whether the I/O path is overloaded or not. So for small block-sized I/Os such as 8K random reads, you generally want the latency to be below or around 10ms, and you can get these data points directly from their respective counters without requiring any additional info.

The problem with having to know the number of spindles before making use of the disk queue length counter is that many times you may not know how many spindles are there or it may take time to get to that info. This is often the case with more advanced I/O subsystems such as a high-end disk array behind a SAN.

Published Monday, November 12, 2007 5:38 PM by Linchi Shea

Attachment(s): disk_q.gif

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Mark Thornton said:

where do I find I/O latency and block size perf counters?

October 1, 2008 2:06 PM
 

deck16 said:

how will i know who eat the hte ave disk queue length

February 6, 2009 12:40 AM

Leave a Comment

(required) 
(required) 
Submit

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement