SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 90

approach that encompasses locking. They have released a SQL Server 2005 Performance Statistics script
via their blog:

How to monitor blocking in SQL Server 2005 and in SQL Server 2000: http://support
.microsoft.com/kb/271509

❑

❑ SQL Server 2005 Performance Statistics Script: http://blogs.msdn.com/psssql/archive/2007/

02/21/sql-server-2005-performance-statistics-script.aspx

The output from these scripts is intended to help troubleshoot the most complex issues, so they can
provide too much information for the casual tuner of locking issues. Easy reviewing of historic
locking behavior can be had from the locking report supplied with DMVStats, which is described near
the beginning of the chapter. Figure 4-11 shows the Analyze Block Info report.

SQL Server 2005 also has a nice little Profiler event called blocked process report , which contains all the
blocking information for blocked processes that have been waiting for a period of time determined by the
blocked process threshold , which is configured with sp_configure . It's off by default but when configured
will trigger blocked process report events that can be picked up by Profiler.

Figure 4-11