SET NOCOUNT ON;

WHILE NOT EXISTS(SELECT *
                 FROM   sys.dm_exec_requests
                 WHERE  blocking_session_id > 0)
BEGIN
   WAITFOR DELAY '00:00:01';
   CONTINUE;
END;


DECLARE @T TABLE 
(
	spid                           smallint ,
	blocked                        int,
	host_name                      nvarchar(128),
	program_name                   nvarchar(128),
	login_name                     nvarchar(128) ,
	nt_user_name                   nvarchar(128),
	last_request_start_time        datetime ,
	last_request_end_time          datetime,
	transaction_isolation_level    smallint ,
	database_id                    smallint ,
	request_database_id            smallint,
	open_transaction_count         int ,
	request_transaction_count      int,
	request_start_time             datetime,
	status                         nvarchar(30),
	command                        nvarchar(32),
	statement_start_offset         int,
	statement_end_offset           int,
	wait_type                      nvarchar(60),
	wait_time                      int,
	last_wait_type                 nvarchar(60),
	wait_resource                  nvarchar(256),
	TSQL_text                      nvarchar(max)
);


INSERT INTO @T
SELECT s.session_id as spid, COALESCE(r.blocking_session_id, 0) AS blocked,
       s.host_name, s.program_name, s.login_name, s.nt_user_name, 
       s.last_request_start_time, s.last_request_end_time, 
       s.transaction_isolation_level, s.database_id, 
       r.database_id AS request_database_id, s.open_transaction_count, 
       r.open_transaction_count AS request_transaction_count,
       r.start_time AS request_start_time, r.status, r.command, 
       r.statement_start_offset, r.statement_end_offset, 
       r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource,
       q.text AS TSQL_text
FROM   sys.dm_exec_sessions AS s
       LEFT OUTER JOIN sys.dm_exec_requests AS r
          ON r.session_id = s.session_id
       OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as q
WHERE  s.is_user_process = 1
  AND  s.session_id <> @@SPID;


WITH 
BLK ( SPID, BLOCKED, LEVEL, BLK_LEVEL, BLK_COUNT, CHAIN_ID )
AS 
(
SELECT spid, blocked, 
       CAST(STR(spid, 5) AS VARCHAR(2000)) AS LEVEL,
       0 AS BLK_LEVEL, 0 AS BLK_COUNT, 
       ROW_NUMBER() OVER(ORDER BY spid) AS CHAIN_ID
FROM   @T R
WHERE  (blocked = 0 OR 
        blocked = spid ) AND EXISTS (SELECT *
                                     FROM   @T AS R2
                                     WHERE  R2.blocked = R.spid AND
                                            R2.blocked <> R2.spid )
UNION  ALL
SELECT R.spid, R.blocked,
       CAST(BLK.LEVEL + STR(R.spid, 5) AS VARCHAR(2000)) AS LEVEL,
       BLK_LEVEL + 1, 
       BLK_COUNT + COUNT(*) OVER(PARTITION BY R.blocked), CHAIN_ID
FROM   @T AS R
       INNER JOIN BLK 
          ON R.blocked = BLK.SPID
WHERE  R.blocked > 0 AND 
       R.blocked <> R.spid 
)
SELECT   SYSUTCDATETIME() AS TRACKING_UTC_TIME,

         CHAIN_ID, BLK_LEVEL AS BLOCK_DEEP, 
         MAX(BLK_COUNT) OVER(PARTITION BY CHAIN_ID) AS TOTAL_BLOCKED,
         REPLICATE(N'|        ', BLK_LEVEL)
            + CASE BLK_LEVEL 
                 WHEN 0 THEN '<> - '
                 ELSE '|--- '
              END 
            + STR(T.spid, 5) AS BLOCKING_TREE,
         T.spid AS session_id,
         T.blocked AS blocker_session_id,
         DB_NAME(T.database_id) AS DATATASE_NAME,

         CASE BLK_LEVEL 
             WHEN 0 THEN CONCAT('KILL ', T.spid, ';') 
             ELSE '' 
          END AS KILL_TO_UNBLOCK,

         T.status,
         T.command,
         T.last_request_start_time,
         T.last_request_end_time,
         T.request_start_time,
         CHOOSE(T.transaction_isolation_level, 
                   '<unknown>', 
                   'UNCOMMITTED', 
                   'COMMITED', 
                   'REPEATABLE READ', 
                   'SERIALIZABLE', 
                   'SNAPSHOT') AS isolation_level,
         T.open_transaction_count AS nb_transaction,
         T.request_transaction_count AS nb_queries,

         T.host_name,
         T.program_name,
         T.login_name,
         T.nt_user_name,

         T.wait_time,
         T.wait_type,
         T.wait_resource,
         T.last_wait_type,

         LTRIM(RTRIM(SUBSTRING(T.TSQL_text, 
                        T.statement_start_offset/2 + 1,  
                        (CASE T.statement_end_offset   
                            WHEN -1 THEN DATALENGTH(T.TSQL_text)  
                            ELSE T.statement_end_offset 
                         END - T.statement_start_offset)/2 + 1
                       )
             )) AS QUERY_text,
         T.TSQL_text                   

FROM     BLK 
         JOIN @T AS T ON BLK.SPID = T.spid
ORDER BY TOTAL_BLOCKED, CHAIN_ID, BLOCK_DEEP, last_request_start_time;