Find blocked and blocking sessions in MSSQL

code Snippets
1

Here are a couple of queries i use extensively while tracking down blocking issues in SQL server.

This query shows all the lead blockers. A lead blocker is a session that is blocking other sessions but is not being blocked itself.
This query is originally from https://www.sqlservergeeks.com/sql-server-how-to-find-the-lead-blocker-culprit-spid-for-all-blocking/
I’ve modified it to include the database name and host process.

/*Show Lead blockers */
SELECT loginame
      ,hostprocess
      ,a.spid
      ,a.program_name
      ,d.name
      ,cpu
      ,memusage
      ,physical_io
      ,*
  FROM master..sysprocesses a
  JOIN sys.databases d ON a.dbid = d.database_id
 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 a.spid;

The query available from the link below will show you all of the sessions that are being blocked.

Both of these are very useful.

5 Likes
2

Thank you Rick! I have added both of these to my “bag of tricks” and Brent Ozar’s “SQL First Responder kit” for finding SQL Server problems. Much appreciated

3

Great Donn. Glad you find it useful.
Brent’s stuff is great.
I highly recommend his classes. A bit expensive but extremely educational.