Getting blocking info in sql server 2005 and 2008 the easy way

http://blogs.microsoft.co.il/blogs/dannyr/archive/2008/10/07/getting-blocking-info-in-sql-server-2005-amp-2008-the-easy-way.aspx

 

management studio :

-- connection 1
use AdventureWorks
go
begin tran

update Person.Contact set Phone = '99999'

this will update the entire contact table ,and note that i did not commit the transaction,in a second connection i am doing the following :

-- connection 2
use AdventureWorks
go
select * from Person.Contact

connection 2 is blocked since contact table is exclusively locked by connection 1 transaction ,so how do i monitor this blocking ? start sql server profiler and select the “blocked Process Report”  in the “Errors And Warnings” event :

note: from envykok

this is first step

 

 

run the trace ,and….. that’s right ,NOTHING is recorded ,why is that ? well ,i forgot to answer an important question,for how long a process is waiting before it is considered blocked?,well there is no correct answer for this question ,in your system  1 second wait time is enough to be considered as blocking ,on your friend system only when a process waits for more then 10 seconds only then it is considered blocked,therefore we have to set a timer somewhere to indicate what in our system ,the amount of seconds that a process is considered blocked,we set this timer by using the “sp_configure “ command one of the properties we can set with the “sp_configure” command is the "blocked process threshold (s)"  (in sql server 2005 it is without the trailing “(S)” ) ,you probably have to set “Show advanced Options”,in order  to view this property

sp_configure "show advanced options",1
go
reconfigure
go
sp_configure "blocked process threshold (s)",4  -- IMPORTANT!!!!by default, this event is closed
go
reconfigure

note: from envykok

this is second step

how to close ' blocked process threshold' event: sp_configure "blocked process threshold (s)",0

please remember to run ' reconfigure', otherwise it will still run as 4

this tells  sql server to issue a “Blocking Process Report”  event for any session that blocked more than 4 seconds ,so i know should get some output on my trace that will look like :

for each of the blocking process report event we get a detailed xml in the text pane ,let’s drill into that more closely :

“<blocked-process-report>
<blocked-process>
  <process id="process58d3558" taskpriority="0" logused="0" waitresource="OBJECT: 7:309576141:0 " waittime="1862752" ownerId="945991" transactionname="SELECT" lasttranstarted="2008-10-07T23:44:52.170" XDES="0x5649de8" lockMode="IS" schedulerid="2" kpid="3808" status="suspended" spid="74" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2008-10-07T23:44:52.107" lastbatchcompleted="2008-10-07T23:44:52.107" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ADVANSOFT_LAP" hostpid="860" loginname="ADVANSOFT_LAP/DannyR" isolationlevel="read committed (2)" xactid="945991" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack>
    <frame line="1" sqlhandle="0x02000000e7b152041386a14bc17dd1750f3e737bb4ec8e76"/>
   </executionStack>
   <inputbuf>
select * from Person.Contact    </inputbuf>
  </process>
</blocked -process>
<blocking -process>
  <process status="sleeping" spid="69" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2008-10-07T23:44:14.543" lastbatchcompleted="2008-10-07T23:44:21.090" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ADVANSOFT_LAP" hostpid="860" loginname="ADVANSOFT_LAP/DannyR" isolationlevel="read committed (2)" xactid="944279" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
   <executionStack/>
   <inputbuf>
begin tran

update Person.Contact set Phone = &apos;99999&apos;
    </inputbuf>
  </process>
</blocking-process>
</blocked-process-report>”

this xml report can be analyzed programmatically of course,but let’s see what we can see here,we get all details on the blocking and blocked processes including commands text,database,spid,sqlhandle and isolationlevel the last 2 are very important since we get only first 255 characters of the blocking and blocked statements ,so we might need to use the sqlhandle to get the full text of the statement ,isolation level is also very important since sometimes the isolation level by itself can cause blocking,for instance the default isolation level in COM+ is Serializable ,which caused a lot of problems in the past.

so by saving the output of this report to a table or preferably to a trace file ,we can examine all blocking scenarios that occurred during the trace run time.

 

so,let’s go guys,the truth is out there….

btw,don’t forget to rollback the transaction in connection 1