Finding the lead blocker in a chain of blocks is a difficult challenge when using sp_who2. There are also a lot of detect blocking scripts that I have run in production environments that seem to block things themselves.
Probably the most efficient way to detect blocks is to query sysprocesses. The following script displays the lead blocker in a chain of blocks:
SELECT spid ,status ,loginame = SUBSTRING(loginame, 1, 12) ,hostname = SUBSTRING(hostname, 1, 12) ,blk = CONVERT(char(3), blocked) ,open_tran ,dbname = SUBSTRING(DB_NAME(dbid),1,10) ,cmd ,waittype ,waittime ,last_batch FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked = 0
This is Kalen Delaney’s code.
You might want to give her the credit.
Calif DBA
Tom,
Thank you. I definitely would have had I known it was hers. Thing is, I found it in a training manual passed out to me in a class from Microsoft and found it was very efficient.
There are certain DMV queries that I don’t know the originator of but in case she is the originator of any other queries, as you mention, I owe her a thanks so I will link to her site from my resources page.
As an additional thanks I’d also like to mention that if anyone has not read any of her internals books, I highly recommend them as they have been completely essential to at least my own understanding of SQL Server.
Kalen Delaney – SQL Server Internals