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

ConfirmingDiskBottlenecks
Once you're suspicious of a disk bottleneck, there are several key system monitor counters that you can
use to confirm whether this is the case. If you have taken a baseline of system performance, you'll be in
a strong position to compare data captured with these same counters against your baseline. If you don't
have a baseline, you can use the guide values provided below to determine whether you should expect
some sort of performance issue.

PhysicalDisk — Avg. Disk Queue Length: Exactly as described in the counter name, this reports
the queue length for each disk. Ensure you select the queue length on the drive letters, not for
_TOTAL object. Although the average disk queue length can't usually be divided by the number
of spindles participating in the array (as it used to be prior to SANs), it still provides a meaning-
ful measure of disk activity. If the disk queue length is consistently above 2, it's likely to impact
performance.

❑

PhysicalDisk — Avg. Disk sec/Read and PhysicalDisk — Avg. Disk sec/Write: This counter
pair refers to the average disk time spent on each read or write. If there's a problem with the
HBA queue depth, controller utilization, or disk performance, you should expect to see higher
than usual times for each of these counters. The nature of your application (reads versus
writes) will determine which or both counters are important to you. If either of these values is
20 ms or greater, you should expect perceived user experience to be affected — although
ideally this should be 10 ms or less. You'll find further details on identifying and resolving
storage performance problems in Chapter 13.

❑

❑ SQLServer:Access Methods — Full scans/sec: This counter will provide visibility of the num-

ber of full index or base table scans requested per second. If you're seeing scan frequency greater
than one per second, this could be indicative of poor or missing indexes.

SQLServer:Access Methods — Page Splits/sec: Page splits are an I/O intensive operation that
occur when there is insufficient space in an 8 KB data page to allow an insert or update operation
to complete. In this circumstance a new page is added and the original data is shared between
the two pages before the insert or update takes place. Too many page splits will harm perfor-
mance. These can be avoided through proper index maintenance and good fill factor selection.

❑

Identifying disk performance problems will lead you to review the design and implementation of the
storage subsystem. Often this will involve some work to characterize the use of the storage and working
with the storage administrators or vendor directly to eliminate performance bottlenecks or to redesign
the storage with a better understanding of your workload.

Configuration-BasedDiskBottlenecks
After you've identified a disk-based bottleneck, potential resolutions are usually fairly readily
available, although implementing these will depend on your hardware configuration and spare capacity.
For example, if you identify a specific drive as having long read and write times, it's a relatively quick
operation to examine what's on the drive and identify what can be moved elsewhere to spread the I/O
load more evenly. If, however, this is a 2 TB database, it may require further planning to understand
what spare capacity is available on the disk storage and logistics planning to evaluate the practicalities of
moving a database of such size.