When you think if Profiler, you think of capturing specific events occuring within SQL Server, usually in a very crude format that is not always easy to understand.
In SQL Server 2005, Profiler has added a new feature that is not only very powerful, but easy to use and interpret. It is called the Blocked Process Report.
Essentially, this report lists all of the blocking going on in your server that exceeds a specific amount of time. For example, let's say that you want to find out all situations where blocking takes longer than 5 seconds. This information would be useful when looking for performance issues in your server.
To create this report in Profiler, you must first "turn on" this Profiler feature. In other words, Profiler does not automatically track blocked processes unless you tell SQL Server to track this information. In addition, you must also specify the minimum time period you want to monitor, say 5 second or 10 seconds.
To turn this feature on, you need to run the following code first:
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
sp_configure 'blocked process threshold', 5 ;
GO
RECONFIGURE ;
GO
In the above example, I have told SQL Server to only look for blocked processes that exceed 5 seconds.
Now that I have turned this feature on, I am now ready to configure Profiler to capture this data.
To do this, I create a new blank trace in Profiler, then select the "Blocked Process Report" event, which is listed under the "Errors and Warnings" event category. Then I run my trace.
If any blocking is occuring on your server while Profile is running, you will see the blocked processed listed on the Profiler screen. Not only can you see the blocked processes, you can see the code this is being blocked, and the code doing the blocking.
This report can be very useful for identifying and fixing blocking problems. Give it a try,