Microsoft SQL Server performance depends heavily on the I/O Subsystem (IOS). Latency in the IOS can result in many performance problems. For example, you may experience slow response times and problems caused by tasks timing out. It is critical that you
monitor your disk usage.
Disk counters that you can monitor to determine disk activity are divided into the following two groups:
Each disk should be monitored individually. Note that using the (_Total) instance can be misleading and mask problem areas. This is because the (_Total) instance sums and averages all of the disks together.
NOTE: If you are using mount points we recommend that you use the Logical Disk object instead of the Physical Disk object. Logical Disk displays the mount point path instead of the physical
In System Monitor, the Avg. Disk sec/Write and Avg. Disk sec/Read counters are considered “primary”. These counters should be examined first and do not need any additional information to evaluate the drive performance. These
counters determine the average latency of an I/O request.
Avg. Disk Sec/Read is the average time in seconds of a read of data from the disk. The following list shows ranges of possible values and what the ranges represent:
Avg. Disk Sec/Write is the average time in seconds of a write of data to the disk. The guidelines for the Avg. Disk Sec/Read values apply here.
NOTE: The numbers listed in this section are for general reference. If you have a very high requirement of application response time on a busy system, meeting the disk response time with these numbers might not be sufficient.
If all or most of the drives report high latency, the bottleneck is probably in the communication medium (such as SAN HBA, Switches, Fiber, Frontend Adaptor CPUs, and Cache). If only one drive or a select few report latency, the bottleneck will generally
be in the JBOD (number of disks). To further examine this, review the secondary counters for the drives reporting high latency. If all drives are below their threshold there is no reason to examine the secondary counters.
NOTE: In System Monitor, it is important to monitor using the maximum field. Using the average field in System Monitor can be misleading.
You should only use the secondary counters for the drive(s) that have high latency. If the drive has acceptable latency, there is no point in moving forward.
Disk Bytes/sec and Disk Transfers/sec are used to determine the size of and number of I/O requests. These counters can help determine if the number of disks or the communication medium is the source of the latency. You can
also use Avg. Disk Queue Length to validate the communication medium. Generally, a value greater than 32 represents a bottleneck that can increase latency.
Disk Transfers/sec is comprised of both Disk Reads/sec and
Disk Writes/sec. You can use these counters to determine if the drive does not have enough supporting disks. When you use these counters you might need to adjust the values for the type of RAID you have implemented. To determine what values
to use, use the following formulas:
For example, if the maximum value for Disk Transfers/sec is 1800, you could determine that the drive will need at least 10 15k RPM disks in its RAID group. Generally, a 15k RPM disk is capable of approximately 180 I/O requests per second (IOPS). 180*10 =
1800. For a higher value you might require more than 10 disks.
NOTE: Consult your hardware vendor to identify the precise amount of IOPS your disks are capable of handling. The average seek time and rotational latency can affect the IOPS output. All disks
are NOT created equal.
If the latency is consistently high, you can determine the root cause by using the secondary counters. If the latency is due to the number of disks, consider the following:
If the latency is due to the communication medium, consider the following:
The following three questions can be used to help identify if there is a storage bottleneck and where it likely is:
In addition to these recommendations, consider tuning queries that generate large amounts of I/O. To identify queries that consume large amounts of I/O, use the
SYS.DM_EXEC_QUERY_STATS DMV. The
dynamic management views (DMVs) have metrics for both reads and writes and are displayed by the query.
You can also include the query plan and SQL command text by joining the
and SYS.DM_EXEC_QUERY_PLAN dynamic management functions with the CROSS APPLY operator.
Following is a sample query using
TOP 25 execution_count, plan_generation_num,
statement_start_offset / 2,
Additionally, the PENDING_DISK_IO_COUNT column in SYS.DM_OS_SCHEDULERS can indicate
disk subsystem problems. You should further investigate any sustained value for each processor. You can use
to identify what the waiting requests are and associate them to database files.
SYS.DM_IO_VIRTUAL_FILE_STATS reports I/O statistics on both data and log files.
Another resource you can use is the
SYS.DM_OS_WAIT_STATS DMV. Use this DMV to determine what the engine is frequently waiting for and
target that area for tuning. You might have an IOS bottleneck if PAGEIOLATCH waits represent some of the highest waits. PAGEIOLATCH waits indicate the amount of time the database engine has been waiting for the IOS. PAGEIOLATCH has various modes and more waits
on PAGEIOLATCH_SH indicate a read bottleneck, whereas PAGEIOLATCH_EX indicates a write bottleneck.
Isolating Disk Activity Created
by SQL Server
You can monitor the following counters to determine the amount of I/O generated by SQL Server components:
In System Monitor, these counters monitor the amount of I/O generated by SQL Server components by examining the following performance areas:
If the values for these counters approach the capacity limit of the hardware I/O subsystem, attempt 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 the 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.
NOTE: These two counters do not include I/O generated by transaction log flushes. Most of the transaction log I/Os are writes.