Find the SPID that is causing extended blocking

On several occasions I get alerts that suggest that extended blocking in occurring in my SQL Server instances. So I need to find the query that is causing blocking. This basically means find the SPID of the query that is causing extended blocking.

If it is a case of just one SPID blocking another then this is easy. Just execute sp_who2 and find the columns where it says blockedby. Or run the following query

select * from sysprocesses where blocked<>0

But what if you have the following senario. You see several SPID that either being blocked or are blocking others. So SPID 15 is blocking 20, then SPID 20 is blocking 33 and so on. This makes it very difficult to read the output from sp_who2 and say what the root query that is causing the blocking. Perhaps killing that one SPID could resolve all blocking. Use the following query to get that SPID. This will give any SPID that is part of blocking but it's request_status is  'GRANT' and not 'WAIT'. If the lock is already acquired then it will be in GRANT status otherwise it will be in WAIT. So this will give the query that is only in GRANT status and hence is causing all the blocking.

SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description
OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='GRANT' and l.request_SESSION_Id not in (SELECT
distinct(l.request_SESSION_Id)
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description
OR
(l.resource_description IS NULL
AND l1.resource_description IS NULL))
and (l.request_status)='WAIT')
order by (l.request_SESSION_Id)