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.