Identifying Blocked Processes with the Blocked Process Report

If you have query delays from blocking, SQL Server has the Blocked Process Report that can generate as an XML document in Profiler, or as a WMI Alert which will provide the Blocked Spid and process information, as well as the blocking spid and process information.  However, before you can use this, you have to set a blocked process threshold to trigger the report.  By default it is set to 0 or off, but you can enable it with the following:

sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 5 ;
GO
RECONFIGURE ;
GO

By using this, you can set a trace for a single event and it will generate a report much like the output of deadlock trace flag 1205/1222.  In fact if you have ever read a deadlock graph from the ErrorLog for either of these trace flags, then the Blocked Process Report will seem really friendly.