Monitoring Server Resources with System Monitor - Professional SQL Server 2005 Performance Tuning - page 21

The length on this queue is exposed as Average Disk Queue Length. Take, for example, the sample server
disk configuration in Table 2-1.

Table 2-1: Sample Database Server Disk Configuration

Drive Purpose Storage RAID Level

C Operating system Internal disks 5 - Striping with parity

D Application Internal disks 5 - Striping with parity

E Database data files SAN storage 10 - Striping with parity, mirrored

F Database transaction log files SAN storage 2 - Disk mirroring

If you were working on a performance issue on the server with the hardware configuration in Table 2-1,
you'd be keen to eliminate any disk input/output (I/O) as the cause of poor performance. If you were
to use Performance Monitor and add the counter for Physical Disk — Average Disk Queue Length, this
would provide a reasonable basis on which to make a decision around whether there was a likely disk
performance issue. However, the server is provided with varying disk sub-systems and structures, and in
order for you to make an informed decision about whether there is a disk performance issue, you'll need
to make sure that your decision is based on valid data. If you were to launch System Monitor, add the
Physical Disk — Average Disk Queue Length, and select the default object. This would provide you with
_TOTAL object. You would almost certainly be considerably better served by selecting the specific drives
that you're monitoring (which might be all of them), but you'll expose individual drive performance,
instead of it potentially being masked in a total of average values (see Figure 2-3).

When using the graph view (as shown previously in Figure 2-1), the scale values on the Y (vertical)
axis can be changed on a per-counter basis, allowing counters with dissimilar scales to be displayed
together. The scale is displayed in the selected counters grid at the bottom of System Monitor, and
don't make the mistake of comparing two similar-looking values that are displaying on different
scales!

The general tab of the System Monitor properties allows you to control the sampling (polling)
interval. Don't underestimate the effect of adjusting the sampling interval, both in terms of the impact on
the target server and with respect to the impact on the shape of the data produced. The default sampling
interval is 1 second, and this is usually suitable for short-term real-time monitoring, although you might
like to reduce this on systems where resources are particularly low. Additionally, if system resources
are very low, you should monitor from a remote server instead of locally as this will require fewer
system resources (inevitably there will be some overhead, such as network traffic). As with any kind
of monitoring, you should try to reach a balance between the overhead placed on the server and perfor-
mance data generated. Your objective should always be to monitor the fewest counters possible while
still revealing enough information about the performance problem to allow diagnosis — or at least a
better understanding!