Blocked and Blocking Processes

The definitive blocking report. This script captures sysprocesses, fingerprints each session with a SHA1 hash of its identifying attributes (so threads of the same logical process group together), and then classifies every session as Blocked, Blocking, or both - reporting wait time, wait type and resource, database, CPU, I/O, memory, login details, and the full SQL text for each. The result is ordered so you can see the whole blocking chain at a glance.

SET NOCOUNT ON;

DECLARE @Processes TABLE
(
	[uid] varbinary(20),
	[spid] smallint NOT NULL,
	[dbid] smallint NOT NULL,
	[blocked] int NOT NULL,
	[waittime] bigint NOT NULL,
	[lastwaittype] nvarchar(128) NOT NULL,
	[waitresource] nvarchar(256) NOT NULL,
	[cpu] bigint NOT NULL,
	[physical_io] bigint NOT NULL,
	[memusage] bigint NOT NULL,
	[login_time] datetime NOT NULL,
	[last_batch] datetime NOT NULL,
	[open_tran] int NOT NULL,
	[status] nvarchar(128) NOT NULL,
	[hostname] nvarchar(128) NOT NULL,
	[program_name] nvarchar(128) NOT NULL,
	[cmd] nvarchar(128) NOT NULL,
	[loginame] nvarchar(128) NOT NULL,
	[sql_handle] binary(20) NOT NULL
)

DECLARE @BlockedAndBlocking TABLE
(
	[UID] varbinary(20) NOT NULL,
	[Id] nvarchar(11) NULL,
	[Threads] int null,
	[Disposition] nvarchar(50) NOT NULL,
	Blocked bit NULL,
	Blocking bit NULL,
	[SPID] smallint NOT NULL,
	[BlockedBy] int NOT NULL,
	[BlockedByUID] varbinary(8000) NULL,
	[WaitTimeMS] bigint NOT NULL,
	[WaitType] nvarchar(128) NOT NULL,
	[WaitResource] nvarchar(256) NOT NULL,
	[Database] sysname NULL,
	[CPU] bigint NOT NULL,
	[IO] bigint NOT NULL,
	[MemoryPages] bigint NOT NULL,
	[LoginTime] datetime NOT NULL,
	[LastBatch] datetime NOT NULL,
	[OpenTransactions] int NOT NULL,
	[Status] nvarchar(128) NOT NULL,
	[HostName] nvarchar(128) NOT NULL,
	[Application] nvarchar(128) NOT NULL,
	[Command] nvarchar(128) NOT NULL,
	[LoginName] nvarchar(128) NOT NULL,
	[SQLHandle] binary(20) NOT NULL
)

INSERT INTO @Processes
SELECT
	HashBytes('SHA1',
		cast(spid as varbinary(8000))
		+ cast(login_time as varbinary(8000))
		+ cast(loginame as varbinary(8000))
		+ cast([program_name] as varbinary(8000))
		+ cast(hostname as varbinary(8000))
		+ sql_handle
	) as [uid],
	spid,
	[dbid],
	blocked,
	LTrim(RTrim(waittime)),
	LTrim(RTrim(lastwaittype)),
	LTrim(RTrim(waitresource)),
	cpu,
	physical_io,
	memusage,
	login_time,
	last_batch,
	open_tran,
	LTrim(RTrim([status])),
	LTrim(RTrim(hostname)),
	LTrim(RTrim([program_name])),
	cmd,
	LTrim(RTrim(loginame)),
	[sql_handle]
FROM
	sys.sysprocesses (NOLOCK)

INSERT INTO @BlockedAndBlocking
SELECT
	Max([Processes].[uid]),
	Max(CAST([Processes].blocked as varchar(10)) + 'A') as Id,

	Count(0) as [Threads],

	CASE WHEN MAX(Blocking.spid) IS NULL THEN 'Blocked' ELSE 'Blocked & Blocking' END [Disposition],

	1 AS Blocked,
	Max(CASE WHEN Blocking.spid IS NOT NULL THEN 1 ELSE 0 END) [Blocking],

	[Processes].spid as [SPID],
	Max([Processes].blocked) as [Blocked By],

	(SELECT MAX([uid]) FROM @Processes WHERE spid = MAX([Processes].blocked)) as [BlockedByUID],

	Sum([Processes].waittime) as [WaitTimeMS],
	Max([Processes].lastwaittype) as [WaitType],
	Max([Processes].waitresource) as [WaitResource],
	Max([Databases].name) as [Database],
	Sum([Processes].cpu) as [CPU],
	Sum([Processes].physical_io) as [IO],
	Sum([Processes].memusage) as [MemoryPages],
	Min([Processes].login_time) as [LoginTime],
	Max([Processes].last_batch) as [LastBatch],
	Max([Processes].open_tran) as [OpenTransaction],
	Max([Processes].[status]) as [Status],
	Max([Processes].hostname) as [HostName],
	Max([Processes].program_name) as [Application],
	Max([Processes].cmd) as [Command],
	Max([Processes].loginame) as [LoginName],
	Max([Processes].sql_handle) as [SQLHandle]
FROM
	@Processes as [Processes]
LEFT OUTER JOIN sys.databases as [Databases] (NOLOCK)
	ON [Databases].database_id = [Processes].[dbid]
LEFT OUTER JOIN (
		SELECT DISTINCT
			[BlockingProcesses].blocked as spid
		FROM
			@Processes as [BlockingProcesses]
		where
			[BlockingProcesses].blocked <> 0
			and [BlockingProcesses].blocked <> [BlockingProcesses].spid
	) as Blocking
	ON Blocking.spid = [Processes].spid
WHERE
	[Processes].blocked <> 0
	and [Processes].blocked <> [Processes].spid
GROUP BY
	[Processes].spid

INSERT INTO @BlockedAndBlocking
SELECT
	MAX([Processes].[uid]),
	MAX(CAST([Processes].spid as varchar(10)) + 'B') as Id,

	Count(0) as [Threads],

	'Blocking' as [Disposition],

	0 AS Blocked,
	1 AS [Blocking],

	[Processes].spid as [SPID],
	MAX([Processes].blocked) as [Blocked By],

	(SELECT TOP 1 [uid] FROM @Processes WHERE spid = MAX([Processes].blocked)) as [BlockedByUID],

	Sum([Processes].waittime) as [WaitTimeMS],
	Max([Processes].lastwaittype) as [WaitType],
	Max([Processes].waitresource) as [WaitResource],
	Max([Databases].name) as [Database],
	Sum([Processes].cpu) as [CPU],
	Sum([Processes].physical_io) as [IO],
	Sum([Processes].memusage) as [MemoryPages],
	Min([Processes].login_time) as [LoginTime],
	Max([Processes].last_batch) as [LastBatch],
	Max([Processes].open_tran) as [OpenTransaction],
	Max([Processes].[status]) as [Status],
	Max([Processes].hostname) as [HostName],
	Max([Processes].[program_name]) as [Application],
	Max([Processes].cmd) as [Command],
	Max([Processes].loginame) as [LoginName],
	MAX([Processes].[sql_handle]) as [SQLHandle]
FROM
	@Processes as [Processes]
LEFT OUTER JOIN sys.databases as [Databases] (NOLOCK)
	ON [Databases].database_id = [Processes].[dbid]
WHERE
	[Processes].blocked = 0
	AND [Processes].spid in
	(
		SELECT
			[BlockingProcesses].blocked
		FROM
			@Processes as [BlockingProcesses]
		WHERE
			[BlockingProcesses].blocked <> 0
			AND [BlockingProcesses].blocked <> [BlockingProcesses].spid
	)
GROUP BY
	[Processes].spid

SELECT
	BAB.[UID],
	BAB.[Disposition],
	BAB.[Threads],
	BAB.[Blocked],
	BAB.[Blocking],
	BAB.[SPID],
	BAB.[BlockedBy],
	BAB.[BlockedByUID],
	BAB.[WaitTimeMS],
	BAB.[WaitType],
	BAB.[WaitResource],
	BAB.[Database],
	BAB.[CPU],
	BAB.[IO],
	BAB.[MemoryPages],
	BAB.[LoginTime],
	BAB.[LastBatch],
	BAB.[OpenTransactions],
	BAB.[Status],
	BAB.[HostName],
	BAB.[Application],
	BAB.[Command],
	BAB.[LoginName],
	BAB.[SQLHandle],
	Txt.[text] as [QueryText]
FROM
	@BlockedAndBlocking as BAB
OUTER APPLY sys.dm_exec_sql_text(BAB.SQLHandle) as Txt
ORDER BY
	Id