Report Description: Blocking and Wait Statistics

 

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: