Identifying the root blocker and the blocking chain

As a DBA, identifying blockings and waitings is one of the primary tasks. I searched in the Google but did not find a useful script. So I came up with one and thought to share it with others.

;WITH requests (session_id, start_time, status, blocking_session_id, 
      database_name, 
      command, sql_text) 
     AS (SELECT session_id, 
                start_time, 
                status, 
                blocking_session_id, 
                Db_name(database_id), 
                command, 
                sql_text = Cast(text AS VARCHAR(max)) 
         FROM   sys.dm_exec_requests WITH (nolock) 
                CROSS apply sys.Dm_exec_sql_text (sql_handle) 
         WHERE  status <> 'Background'), 
     blocking (session_id, start_time, status, blocking_session_id, command, 
     sql_text, rownum, levelrow) 
     AS (SELECT r1.session_id, 
                r1.start_time, 
                r1.status, 
                r1.blocking_session_id, 
                r1.command, 
                r1.sql_text, 
                Row_number() 
                  OVER ( 
                    ORDER BY r1.session_id), 
                0 AS LevelRow 
         FROM   requests r1 
                INNER JOIN requests r2 
                        ON r1.session_id = r2.blocking_session_id 
         WHERE  r1.blocking_session_id = 0 
         UNION ALL 
         SELECT r3.session_id, 
                r3.start_time, 
                r3.status, 
                r3.blocking_session_id, 
                r3.command, 
                r3.sql_text, 
                b.rownum, 
                b.levelrow + 1 
         FROM   requests r3 
                INNER JOIN blocking b 
                        ON r3.blocking_session_id = b.session_id 
         WHERE  r3.blocking_session_id > 0) 
SELECT * 
FROM   blocking 
ORDER  BY rownum, 
          levelrow 

Alternatively you can write the output of this to a table as a schedule job and the results can be analyzed later. This is really useful to identify any blocking patterns of the database server and fix them if they persist.