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

 

image

 

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 :

image

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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值