Udostępnij za pośrednictwem

Monitoring Disk Usage

  • Artykuł

Microsoft SQL Server uses Microsoft Windows operating system input/output (I/O) calls to perform read and write operations on your disk. 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 activity involves two areas of focus:

  • Monitoring Disk I/O and Detecting Excess Paging
  • Isolating Disk Activity That SQL Server Creates

Monitoring Disk I/O and Detecting Excess Paging

Two counters that can be monitored to determine disk activity are:

  • PhysicalDisk: % Disk Time
  • PhysicalDisk: Avg. Disk Queue Length

In System Monitor, 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 PhysicalDisk: 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 that make up the physical disk. Most disks have one spindle, although redundant array of independent disk (RAID) devices usually have more than one spindle. A hardware RAID device appears as one physical disk in System Monitor. RAID devices created through software appear as multiple instances in System Monitor.

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 doing one of the following:

  • Using a faster disk drive.
  • Moving some files to an additional disk or server.
  • Adding 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, 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 rather than the Physical Disk counters. Look at the logical disk counters to help determine which files are frequently accessed. After you have found the disks with high levels of read/write activity, look at the read-specific and write-specific counters to learn the type of disk activity that causes the load on each logical volume; for example, Logical Disk: Disk Write Bytes/sec.

Isolating Disk Activity Created by SQL Server

Two counters that can be monitored to determine the amount of I/O generated by SQL Server components are:

  • SQL Server:Buffer Manager:Page reads/sec
  • SQL Server:Buffer Manager:Page writes/sec

In System Monitor, these counters monitor the amount of I/O generated by SQL Server components by examining the following performance areas:

  • Writing pages to disk
  • Reading pages from disk

If the values for these counters approach the capacity limit 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 adding memory. For example, you can use Database Engine Tuning Advisor to analyze typical SQL Server workloads and produce recommendations for indexes, indexed views, and partitioning to improve server performance. For more information about Database Engine Tuning Advisor, see Tuning the Physical Database Design.