Monitoring Disk Activity
Microsoft® SQL Server™ uses Microsoft Windows NT® 4.0 or Windows® 2000 I/O calls to perform disk reads and writes. SQL Server manages when and how disk I/O is performed, but the Windows operating system performs the underlying I/O operations. The I/O subsystem includes the system bus, disk controller cards, disks, tape drives, CD-ROM drive, and many other I/O devices. Disk I/O is frequently the cause of bottlenecks in a system.
Monitoring Disk I/O and Detecting Excess Paging
Two of the counters that can be monitored to determine disk activity include:
- PhysicalDisk: % Disk Time
- PhysicalDisk: Avg. Disk Queue Length
In System Monitor (Performance Monitor in Windows NT 4.0), the PhysicalDisk: % Disk Time counter monitors the percentage of time that the disk is busy with read/write activity. If the PhysicalDisk: % Disk Time counter is high (more than 90 percent), check the Physical Disk: Current Disk Queue Length counter to see how many system requests are waiting for disk access. The number of waiting I/O requests should be sustained at no more than 1.5 to 2 times the number of spindles making up the physical disk. Most disks have one spindle, although redundant array of inexpensive disks (RAID) devices usually have more. A hardware RAID device appears as one physical disk in System Monitor; RAID devices created through software appear as multiple instances.
Use the values of the Current Disk Queue Length and % Disk Time counters to detect bottlenecks within the disk subsystem. If Current Disk Queue Length and % Disk Time counter values are consistently high, consider:
- Using a faster disk drive.
- Moving some files to an additional disk or server.
- Adding additional disks to a RAID array, if one is being used.
If you are using a RAID device, the % Disk Time counter can indicate a value greater than 100 percent. If it does, use the PhysicalDisk: Avg. Disk Queue Length counter to determine how many system requests, on average, are waiting for disk access.
Applications and systems that are I/O-bound may keep the disk constantly active.
Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. In Windows NT 4.0 or Windows 2000, paging is caused by:
- Processes configured to use too much memory.
- File system activity.
If you have more than one logical partition on the same hard disk, use the Logical Disk counters instead of the Physical Disk counters. Looking at the logical disk counters will help you determine which files are heavily accessed. After you have found the disks with high levels of read/write activity, look at the read-specific and write-specific counters (for example, Logical Disk: Disk Write Bytes/sec) for the type of disk activity that is causing the load on each logical volume.
Isolating Disk Activity Created by SQL Server
To determine the amount of I/O generated by SQL Server components, examine the following performance areas:
- Writing pages to disk
- Reading pages from disk
The number of page reads and writes that SQL Server performs can be monitored using the SQL Server: Buffer Manager Page Reads/sec and Page Writes/sec counters. If these values start to approach the capacity of the hardware I/O subsystem, try to reduce the values by tuning your application or database to reduce I/O operations (such as index coverage, better indexes, or normalization), increasing the I/O capacity of the hardware, or by adding memory.