How Fast and Robust Is Your Storage? - Professional SQL Server 2005 Performance Tuning - page 414

are great, latencies from 5-10 mSec are acceptable, and 10-20 mSec may be acceptable. Generally,
however, 20 mSec is considered to be the outer limit of acceptability. Latencies of greater than 20mSec
are a warning sign that the storage is being over stressed. The SQL Server Best Practices team has written
a number of papers related to storage performance that all recommend latencies around these numbers.
Just to recap on those numbers as they are so important, here they are again:

❑ Less than 5 mSec — Excellent

❑ 5-10 mSec — Good

❑ 10-20 mSec — Poor

❑

Greater than 20 mSec — Bad

As mentioned previously, always look at the MB/Sec and latency together. When you're looking at
disk throughput, discount any throughput that's achieved at latencies of over 10 mSec.

Best Practice
When reading throughput, always consider the MB/Sec and latency together

DiskQueueLength
This is another indicator you need to consider when looking at the throughput. It often will go hand in
hand with latency. Disk Queue Length is the length of the disk queue, or the number of I/O requests
the disk has queued up while it has been busy working on the current I/O request. You will find any
number of different recommendations for what this should be, and it's very difficult to figure out exactly
what number is good as there are many places this value can be observed from the Perfmon logical
disk counters, through the physical disk counters, and down further into the storage stack if an array
controller or intelligent storage subsystem exposes additional counters.

Best Practice
Disk Queue Length should be 0 or 1 at the disk. Consider the storage structure and
where in the storage stack this counter is being measured.

Measuring I/OPerformance
In this section you will learn about the tools you can use to measure I/O performance. There are two
primary sets of tools you can for this: Perfmon counters and SQL Server DMVs.

PerfmonCounters
There are just a handful of Perfmon counters you are interested in when looking at storage performance.
What is a little confusing about them is that the same counters appear under two different counter
objects: physical disk and logical disk.

Let's first go through the basic counters; then you'll see a little more about the difference between the
physical and logical disk counters.