The purpose
of the Blocking and Wait Statistics top-level report is to provide a view of lock
blocking and other resource wait bottlenecks. You would typically use this report when
server performance is impaired, but the server is not CPU-bound. Start with the Profiler Trace Analysis report if
you have a performance problem characterized by sustained high CPU use (most high
CPU problems stem from a particular expensive query).
By default,
the report reflects the most recent 4 hour period. You can narrow or expand the time window by
clicking the Parameters button on the report toolbar.
The
Blocking and Wait Statistics report requires a SQL Server 2005 instance, as it is
based on data collected from dynamic management views that do not exist on
prior versions. It is recommended that
you use live data collection to gather the data on which this report is based (answer Yes when SQL Nexus
asks whether you want to start the data collector).
The first
chart on the report reflects the top 5 wait categories that contributed to
resource waits on the system. (A “wait
category” is either a SQL Server wait type or a family of related wait types. For example, all lock wait types are grouped together
into a single wait category. For a
description of SQL Server 2005 wait types, see the sys.dm_os_wait_stats
Books Online topic.) If you are
experiencing a major lock blocking problem, high lock waits should show up in
this chart. You may also see other wait
categories here. Some examples:
The second
chart on this report shows wait time for the most common wait categories
graphed over time. If server performance
degraded at some point during the data capture, you can use this chart to
identify the wait category that increased near the beginning of the problem
period.
The third
and final section of this report shows distinct blocking chains. The analysis script on which this report is
based makes an attempt to recognize when the same blocking chain persists for
an extended period so that each chain is only reported once. A high level summary of each blocking chain is
shown, including the duration of the blocking incident, the maximum number of
threads that were involved in the blocking incident, and the “Blocking Type”. This last value provides information about the
type of resource that was locked. Possible
values include “LOCK BLOCKING” for blocking incidents involving lock resources;
“PAGELATCH_* WAITS” for non-I/O page latch contention (see the “Page Latch
(non-I/O)” bullet point, above, for more information); and “PAGEIOLATCH_* WAITS”,
which means that SQL is waiting for a data page to be read from or written to disk.
Click on the
start time for a blocking chain to drill into a detailed view of that blocking
incident. The Blocking Chain Detail
report shows information like application name, login, active query text, transaction
start time, and hostname for the session that is at the head of the blocking
chain; and a list of all of the requests that are blocked by this session.
Other comments: