find_head_of_blocking_chain

WITH blocking_sessions
    AS (SELECT sess.session_id
             , req.request_id
             , LEFT(ISNULL(req.wait_type, ''), 50)     AS 'wait_type'
             , LEFT(ISNULL(req.wait_resource, ''), 40) AS 'wait_resource'
             , LEFT(req.last_wait_type, 50)            AS 'last_wait_type'
             , req.cpu_time                            AS 'request_cpu_time'
             , req.wait_time
             , req.blocking_session_id
             , req.command
             , req.plan_handle
             , req.[sql_handle]
             , conn.most_recent_sql_handle

        FROM sys.dm_exec_sessions AS sess
                 LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
                 LEFT OUTER JOIN sys.dm_exec_connections AS conn ON conn.session_id = sess.session_id)
   , blocking_tree
    AS (SELECT head.session_id AS head_blocker_session_id
             , head.session_id AS session_id
             , head.blocking_session_id
             , head.wait_type
             , head.wait_time
             , head.wait_resource
             , head.plan_handle
             , head.sql_handle
             , head.most_recent_sql_handle
             , 0               AS [Level]
        FROM blocking_sessions AS head
        WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
          AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM blocking_sessions WHERE blocking_session_id != 0)
        UNION ALL
        SELECT h.head_blocker_session_id,
               blocked.session_id,
               blocked.blocking_session_id,
               blocked.wait_type,
               blocked.wait_time,
               blocked.wait_resource,
               h.plan_handle,
               h.sql_handle,
               h.most_recent_sql_handle,
               [Level] + 1
        FROM blocking_sessions AS blocked
                 INNER JOIN blocking_tree AS h ON h.session_id = blocked.blocking_session_id AND h.session_id !=
                                                                                                        blocked.session_id
        WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET')
           OR h.wait_type IS NULL)
SELECT bh.head_blocker_session_id, bh.session_id, bh.blocking_session_id, outer_text.event_info, txt.text AS blocker_query_or_most_recent_query,
bh.wait_type, bh.wait_time, bh.wait_resource
FROM blocking_tree AS bh
         OUTER APPLY sys.dm_exec_sql_text(ISNULL([sql_handle], most_recent_sql_handle)) AS txt
        OUTER APPLY  sys.dm_exec_input_buffer (head_blocker_session_id, null) outer_text;