Database Reference
In-Depth Information
You can see not only the cumulative time that particular waits have accumulated but also a count of how often
they have occurred and the maximum time that something had to wait. From here, you can identify the wait type
and begin troubleshooting. One of the most common types of waits is I/O. If you see ASYNC_I0_C0MPLETI0N,
IO_C0MPLETI0N, LOGMGR, WRITELOG, or PAGEIOLATCH in your top ten wait types, you may be experiencing I/O
contention, and you now know where to start working. For a more detailed analysis of wait types and how to use them
as a monitoring tool within SQL Server, read the Microsoft white paper “SQL Server 2005 Waits and Queues”
(
http://bit.ly/1e1I38f
)
. Although it was written for SQL Server 2005, it is still largely applicable to newer versions
of SQL Server. You can always find information about more obscure wait types by going directly to Microsoft through
MSDN support (
http://bit.ly/1hBzLrZ
). Finally, when it comes to wait types, Bob Ward's repository (collected at
http://bit.ly/1afzfjC
)
is a must-read even though it's not being maintained currently.
Hardware Resource Bottlenecks
Typically, SQL Server database performance is affected by stress on the following hardware resources:
•
Memory
•
Disk I/O
•
Processor
•
Network
Stress beyond the capacity of a hardware resource forms a bottleneck. To address the overall performance of
a system, you need to identify these bottlenecks because they form the limit on overall system performance.
Identifying Bottlenecks
There is usually a relationship between resource bottlenecks. For example, a processor bottleneck may be a symptom
of excessive paging (memory bottleneck) or a slow disk (disk bottleneck). If a system is low on memory, causing
excessive paging, and has a slow disk, then one of the end results will be a processor with high utilization since the
processor has to spend a significant number of CPU cycles to swap pages in and out of the memory and to manage the
resultant high number of I/O requests. Replacing the processors with faster ones may help a little, but it is not the best
overall solution. In a case like this, increasing memory is a more appropriate solution because it will decrease pressure
on the disk and processor. In fact, upgrading the disk is probably a better solution than upgrading the processor. If you
can, decreasing the workload could also help, and, of course, tuning the queries to ensure maximum efficiency is also
an option.
One of the best ways of locating a bottleneck is to identify resources that are waiting for some other resource to
complete its operation. You can use Performance Monitor counters or DMOs such as sys.dm_os_wait_stats to gather
that information. The response time of a request served by a resource includes the time the request had to wait in the
resource queue as well as the time taken to execute the request, so end user response time is directly proportional to
the amount of queuing in a system.
Another way to identify a bottleneck is to reference the response time and capacity of the system. The amount of
throughput, for example, to your disks should normally be something approaching what the vendor suggests the disk
is capable of. So, measuring information such as disk sec/transfer will indicate when disks are slowing down because
of excessive load.
Not all resources have specific counters that show queuing levels, but most resources have some counters that
represent an overcommittal of that resource. For example, memory has no such counter, but a large number of hard
page faults represents the overcommittal of physical memory (hard page faults are explained later in the chapter in
the section “Pages/Sec and Page Faults/Sec”). Other resources, such as the processor and disk, have specific counters
to indicate the level of queuing. For example, the counter Page Life Expectancy indicates how long a page will stay in
