Blocking Processes (lead blocker)

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
    ,sp.STATUS
    ,loginame   = SUBSTRING(loginame, 1, 12)
    ,hostname   = SUBSTRING(hostname, 1, 12)
    ,blk        = CONVERT(CHAR(3), blocked)
    ,open_tran
    ,dbname     = SUBSTRING(DB_NAME(sp.dbid),1,10)
    ,cmd
    ,waittype
    ,waittime
    ,last_batch
    ,SQLStatement       =
        SUBSTRING
        (
            qt.text,
            er.statement_start_offset/2,
            (CASE WHEN er.statement_end_offset = -1
                THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
                ELSE er.statement_end_offset
                END - er.statement_start_offset)/2
        )
FROM master.dbo.sysprocesses sp
LEFT JOIN sys.dm_exec_requests er
    ON er.session_id = sp.spid
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

This will also display the SQL Statement that is the offending blocker.

5 comments
Anonymous 15 Apr 2012 at 3:19 am

I have pasted this and run. it shows 0 rows affected. Please help

John P 22 Feb 2012 at 8:17 am

Absolutely love the code you have published! Thanks so much. Below is one change I made so that I could see the wait type description vs. the wait type code. Thanks again.

changed this:
,waittype
to this:
,er.wait_type

Al 26 Oct 2011 at 6:35 pm

shouldn’t the query be changed from
“WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0″
to
“WHERE spid IN (SELECT spid FROM master.dbo.sysprocesses)
AND blocked != 0″

?

Tom 26 May 2010 at 1:26 pm

This is Kalen Delaney’s code.
You might want to give her the credit.

Calif DBA

Derek Dieter 26 May 2010 at 1:56 pm

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 helpful in my day to day troubleshooting. I’d imagine one of the unfortunate trade-offs in writing code for Microsoft is that you remain nameless, however I’m sure there are great perks as well.

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 do owe her a thanks so I will link to her site from my resources page.

As an additional plug for Kalen, I will mention that if anyone has not read any of her internals books, I recommend them as they have been completely essential to my own understanding of SQL Server.

Kalen Delaney – SQL Server Internals

Featured Articles

 Site Author