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

Figure 4-10
Monitoring for Blocking Locks
You've already seen how to view and troubleshoot blocking locks using DMVs. Monitoring of a busy
system needs a bit more forethought than just viewing dynamic locking or reactively troubleshooting.
Historic data is the most valuable asset to have when troubleshooting, and proactively gathering a
baseline of locking activity can yield positive results when you analyze the output.
Microsoft knowledge base article 271509 contains a script to create a stored procedure called sp_blocker_
pss80 , which is often called simply the blocker script . It was created for SQL Server 2000 and gathers all
the information you need to analyze blocking issues. You need to run the stored procedure in a loop and
output the information to a file at set intervals. The article can help you to set this up.
The blocker script is used by Microsoft support staff to monitor locking over a certain period and
then take the results to analyze offline. It also works for SQL Server 2005 but doesn't make use of
the new features and DMVs. For SQL Server 2005 they decided to take a wider performance tuning