Hi All,

This is the query I have

SELECT

[Head Blocker SPID] = s.session_id,

[Number of Statements Blocked] = count(r2.blocking_session_id),

[Head Blocker SQL Statement] =(

SELECT text AS [text()]

FROM sys.dm_exec_sql_text(p.sql_handle)

FOR XML PATH(''), TYPE

),

[Head Blocker Run Time (seconds)] = (datediff(second, s.last_request_start_time, getdate())),

[Source Host Name] = ISNULL(s.host_name, N''),

[Login Name] = s.login_name

FROM sys.dm_exec_sessions s LEFT OUTER JOIN sys.dm_exec_connections c ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t ON (r.session_id = t.session_id AND r.request_id = t.request_id)

LEFT OUTER JOIN

(

SELECT *, ROW_NUMBER() OVER (PARTITION BY waiting_task_address ORDER BY wait_duration_ms DESC) AS row_num

FROM sys.dm_os_waiting_tasks

) w ON (t.task_address = w.waiting_task_address) AND w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2 ON (s.session_id = r2.blocking_session_id)

LEFT OUTER JOIN sys.sysprocesses p ON (s.session_id = p.spid)

WHERE r2.session_id IS NOT NULL AND (r.blocking_session_id = 0 OR r.session_id IS NULL)

GROUP BY s.session_id,s.login_name,s.host_name,s.last_request_start_time,p.sql_handle

Can somebody please help me with one thing.. in this query I am able to get all the spids that are "DIRECTLY" being blocked. But I need to tweak it in a way that this will give me all the spids which are directly or indirectly blocked for eg – If SPID is blocking SPIDs 56,74 and 56 is blocking 83,34 then the last column(No. of statements it is blocking directly or indirectly) should give ‘4’.

Thanks in advance.

VJ