Recently I’ve been working on an issue where a query was being blocked.  In order to get the specifics on this it’s possible to use the Blocked progress report in the SQL Profiler.

To do this you need to first enable advanced options by running:

SP_CONFIGURE’show advanced options’,1 ;
GO

Then run:

RECONFIGURE;
GO

After turning that one, you need to set the blocked process threshold.  This is the number of seconds between checks to see if there is blocking going on. 

SP_CONFIGURE’blocked process threshold’,10 ;
GO

This will set it for 10 seconds.  You could change it to something lower if you want.  When you are done testing you should change it back to 0 (disabled).

After setting the threshold you need to reconfigure again:

RECONFIGURE;
GO

Then pick the Blocked process report from SQL Profiler:

The possible values of the Mode value are:

0=NULL
1=Sch-S
2=Sch-M
3=S
4=U
5=X
6=IS
7=IU
8=IX
9=SIU
10=SIX
11=UIX
12=BU
13=RangeS-S
14=RangeS-U
15=RangeI-N
16=RangeI-S
17=RangeI-U
18=RangeI-X
19=RangeX-S
20=RangeX-U
21=RangeX-X