Database Reference
In-Depth Information
Chapter 3
Disk Performance Analysis
The disks and the disk subsystem, which includes the controllers and connectors and management software, are one
of the single slowest parts of any computing system. Over the years, memory has become faster and faster. The same
can be said of CPUs. But disks, except for some of the radical improvements we've seen recently with technologies
such as solid-state disks (SSDs), have not changed that much; disks are still one of the slowest parts of most systems.
This means you're going to want to be able to monitor your disks to understand their behavior. In this chapter, you'll
explore areas such as the following:
•
Using system counters to gather disk performance metrics
•
Using other mechanisms of gathering disk behavior
•
Resolving disk performance issues
Disk Bottleneck Analysis
SQL Server can have demanding I/O requirements, and since disk speeds are comparatively much slower than
memory and processor speeds, a contention in disk resources can significantly degrade SQL Server performance.
Analysis and resolution of any disk resource bottleneck can improve SQL Server performance significantly.
Disk Counters
To analyze disk performance, you can use the counters shown in Table
3-1
.
Table 3-1.
Performance Monitor Counters to Analyze I/O Pressure
Object(lnstance[,lnstanceN])
Counter
Description
Value
PhysicalDisk(Data-disk,
Log-disk)
% Disk Time
Percentage of time disk was
busy
Average value < 85%, but
compare to baseline
Current Disk Queue
Length
Number of outstanding
disk requests at the time
performance data is collected
Compare to baseline
Avg. Disk Queue
Length
Average number of queued
disk requests during the
sample interval
Compare to baseline
(
continued
)
