As you know the new blocked process report in SQL Server 2005 and beyond is awesome. Now if you have lots of blocked processes being reported it can be a bit overwheleming. How can you quickly tell if it’s the same senario over and over again or if your hitting multiple scenarios. I don’t know about you but I’m cheap and I don’t want to buy a tool so I’ll tell you how to do this yourself.
1) Run a profiler trace for blocked process event on the SQL Instance in question.
2) Save the profile to a table.
3) You need to save the data again from the table in step 2 as xml just use the textdata column but it needs to be an XML Data Type.
Here is a sample.
StartTime,
CONVERT(xml,textdata) As BlockedRPT, Servername
INTO ALLBLOCKS
from dbo.myProfileTable
where EventClass = 137
4) Run the following SQL to get the distinct Blocking and being Blocked statements
distinct
X.x.value(‘data(.)’,'varchar(5000)’) AS BlockedSQLcmd,
Y.x.value(‘data(.)’,'varchar(5000)’) AS BlockingSQLcmd
FROM ALLBLOCKS a
CROSS APPLY
blockedRpt.nodes(‘/blocked-process-report/blocked-process/process’) AS X(x)
CROSS APPLY
blockedRpt.nodes(‘/blocked-process-report/blocking-process/process’) AS Y(x)
4) Run the following SQL to get all the details you need about each blocked process report.
convert(date,starttime),
X.x.value(‘@currentdb’, ‘nvarchar(45)’) Blockeddb,
X.x.value(‘@waitresource’, ‘nvarchar(45)’) BlockedObject ,
X.x.value(‘@lockMode’, ‘nvarchar(45)’) BlockedBlockLockMode,
X.x.value(‘@hostname’,'nvarchar(45)’) BlockedFromHOst,
X.x.value(‘@loginname’,'nvarchar(45)’) BlockedFromLogin,
X.x.value(‘@isolationlevel’,'nvarchar(45)’) BlockedFromIso,
X.x.value(‘data(.)’,'varchar(5000)’) AS BlockedSQLcmd,
Y.x.value(‘@waitresource’, ‘nvarchar(45)’) BlockingObject ,
Y.x.value(‘@hostname’,'nvarchar(45)’) BlockingFromHOst,
Y.x.value(‘@loginname’,'nvarchar(45)’) BlockingFromLogin,
Y.x.value(‘@isolationlevel’,'nvarchar(45)’) BlockingFromIso,
Y.x.value(‘data(.)’,'varchar(5000)’) AS BlockingSQLcmd
FROM ALLBLOCKS a
CROSS APPLY
blockedRpt.nodes(‘/blocked-process-report/blocked-process/process’) AS X(x)
CROSS APPLY
blockedRpt.nodes(‘/blocked-process-report/blocking-process/process’) AS Y(x)
Posted by motherofallgeeks 
