SQL Server: How to find the lead blocker/ culprit spid for all blocking?

Hi All,

Question of the day? How to identify the lead blocking transaction out of all Blocking statements?

Blocking is not painful till there are only a few transactions being blocked and the blocking gets resolved within few Milliseconds or seconds.

Now imagine a situation where the Database in subject is highly transactional in nature and you got some blocking which is impacting business, now your task is to identify and highlight the application\user to the business which is creating all this blocking(so that the code can be modified)…..so simple isn’t it.

You started task by first checking how many connections are there on this Database, and you found 600+ connections.

You said ok, let’s check the connections which are getting blocked to identify by using the command below:

select * from sys.sysprocesses where  spid >= 50 and blocked <> 0

You got surprised to see there were around 100+ rows (transactions) being blocked, now what? am i supposed to check each and every transaction to see which one is actually blocking the other one and so-on and so-forth?

No, this is where the below command comes to rescue:

This will list down the transaction which is the lead blocker and the real culprit for all that blocking.

select loginame, cpu, memusage, physical_io, * 
  from  master..sysprocesses a
 where  exists ( select b.*
    from master..sysprocesses b
    where b.blocked > 0 and
   b.blocked = a.spid ) and not
 exists ( select b.*
     from master..sysprocesses b
    where b.blocked > 0 and
   b.spid = a.spid ) 
order by spid

Regards

Sarabpreet Anand

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook