🧱 Identifying Head Blockers in SQL Server

May 31, 2022

Many DBAs find it hard to quickly identify what is blocking other processes in SQL Server. Here's a standalone script that helps you identify the head blockers and the sessions they are blocking. It uses sp_who2, DBCC INPUTBUFFER, and a bit of cursor logic to tie it all together.

🔍 Tip: Run this in SQL Server Management Studio (SSMS) and set the output to "Results in Text" using CTRL+T.

SET NOCOUNT ON
DECLARE @spid VARCHAR(10)
DECLARE @blkby VARCHAR(10)
DECLARE @stmt VARCHAR(100)

IF NOT EXISTS (SELECT TOP 1 name FROM tempdb..sysobjects WHERE name LIKE '#temp%')
BEGIN
   CREATE TABLE #temp (
      spid INT, status VARCHAR(100), login VARCHAR(50), hostname VARCHAR(25),
      blkby VARCHAR(10), dbname VARCHAR(25), command VARCHAR(100),
      cputime INT, diskio INT, lastbatch VARCHAR(25),
      programname VARCHAR(255), spid2 INT
   )
END
ELSE
BEGIN
   TRUNCATE TABLE #temp
END

INSERT INTO #temp
EXEC sp_who2

DECLARE curs CURSOR FOR
SELECT CONVERT(VARCHAR(10), spid), blkby
FROM #temp
WHERE blkby NOT LIKE '%.%' -- Exclude background tasks

OPEN curs

FETCH NEXT FROM curs INTO @spid, @blkby
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @stmt = 'DBCC INPUTBUFFER(' + @blkby + ')'
   RAISERROR('SPID:%s is Blocking with the following statement', 0, 1, @blkby) WITH NOWAIT
   EXEC(@stmt)

   RAISERROR('SPID that is Blocked:%s', 0, 1, @spid) WITH NOWAIT
   SET @stmt = 'DBCC INPUTBUFFER(' + CONVERT(VARCHAR(10), @spid) + ')'
   EXEC(@stmt)

   FETCH NEXT FROM curs INTO @spid, @blkby
END

CLOSE curs
DEALLOCATE curs

🆕 Better Options: Using `sp_BlitzWho` and `sp_WhoIsActive`

While the script above works, it’s even easier now with Brent Ozar’s sp_BlitzWho and Adam Machanic’s sp_WhoIsActive. These tools give far better context, wait stats, and query text.

✅ sp_BlitzWho

Download here

-- Run this to see blocking sessions clearly
EXEC sp_BlitzWho @ShowSleepingSPIDs = 1;

Key Benefits:

Highlights blocking sessions in red
Shows query text, wait types, open transactions
Easy to filter by login, host, database

✅ sp_WhoIsActive

Download here

-- Run this to show real-time blocking
EXEC sp_WhoIsActive @find_block_leaders = 1, @get_full_inner_text = 1;

Key Benefits:

Shows blocking chains
Includes exact SQL text and wait types
Shows how long each session has been running

🧠 Conclusion

Use the script above for quick troubleshooting if nothing else is available. But for ongoing DBA work, use sp_BlitzWho and sp_WhoIsActive — they make tracking down blockers dramatically easier, with better output and less noise.

🔗 Questions? Leave a comment below or reach out!

Search This Blog