USE msdb
GO

CREATE SCHEMA S_SURVEY;
GO

CREATE TABLE S_SURVEY.T_LOCKING_TREE_LKT
(
LKT_ID                 BIGINT IDENTITY(1,1) PRIMARY KEY,
LKT_SESSION            UNIQUEIDENTIFIER NOT NULL, -- session identifier 
LKT_TRACKING_UTC_TIME  DATETIME2(7) NOT NULL,     -- event universal time
LKT_TRACKING_TIME      DATETIME NOT NULL,         -- event local time
LKT_CHAIN_ID           SMALLINT NOT NULL,         -- blocking chain identifier
LKT_BLOCK_DEEP         SMALLINT NOT NULL,         -- blocking deep
LKT_TOTAL_BLOCKED      SMALLINT NOT NULL,         -- blocked count 
LKT_BLOCKING_TREE      VARCHAR(max) NULL,         -- the blocking tree
LKT_SESSION_ID         SMALLINT NOT NULL,         -- session_id identifier
LKT_BLOCKER_ID         SMALLINT NULL,             -- blocker session_id
LKT_DATATASE_NAME      NVARCHAR(128) NULL,        -- database name
LKT_KILL_TO_UNBLOCK    VARCHAR(32) NULL,          -- TSQL to kill leader
LKT_STATUS             VARCHAR(32) NULL,          -- status (running, ...)                       
LKT_COMMAND            VARCHAR(32) NULL,          -- SQL command type
LKT_LAST_QUERY_START   DATETIME NULL,             -- datetime of query start
LKT_ISOLATION_LEVEL    VARCHAR(16) NULL,          -- isolation level
LKT_TRANSACTION_COUNT  INT NULL,                  -- nested tran count
LKT_QUERY_COUNT        INT NULL,                  -- tran query count            
LKT_HOST_NAME          NVARCHAR(128) NULL,        -- host
LKT_PROGRAM_NAME       NVARCHAR(128) NULL,        -- program
LKT_LOGIN_NAME         NVARCHAR(128) NULL,        -- SQL login
LKT_NT_USER_NAME       NVARCHAR(128) NULL,        -- system login
LKT_WAIT_TIME_MS       INT NULL,                  -- wait duration ms
LKT_WAIT_TYPE          NVARCHAR(60) NULL,         -- wait kind
LKT_WAIT_RESOURCE      NVARCHAR(256) NULL,        -- wait resource
LKT_LAST_WAIT_TYPE     NVARCHAR(60) NULL,         -- last wait kind
LKT_QUERY_TEXT         NVARCHAR(max) NULL,        -- SQL of query
LKT_TSQL_TEXT          NVARCHAR(max) NULL         -- Transact SQL
);
GO

USE msdb;
GO

CREATE OR ALTER PROCEDURE S_SURVEY.P_SQLPRO_TREEBLOKERS
   @DURATION_S_ALERT    SMALLINT = 30,  -- informs if duration >=
   @BLOCKED_COUNT_ALERT TINYINT  = 5,   -- informs if session count >=
   @BLOCKED_DEEP_ALERT  TINYINT  = 3,   -- informs if deep >=
   @DELETE_DEEP_DAYS    TINYINT  = 90   -- delete old traces in days >
AS

/******************************************************************************
* MODULE  : SURVEY                                                            *
* NATURE  : PROCEDURE                                                         *
* DB      : msdb                                                              *
* SCHEMA  : S_SURVEY                                                          *
* OBJECT  : P_SQLPRO_TREEBLOKERS                                              *
* CREATE  : 2023-02-11                                                        *
* AUTHOR  : Frédéric BROUARD - SQLpro                                         *
* VERSION : 1                                                                 *
* SYSTEM  : NO                                                                *
* VALID   : 2012-...                                                          *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* expert technical blog : http://mssqlserver.fr - from book : SQL Server 2014 *
*******************************************************************************
* PURPOSE : capture blockin trees and store it into table                     *
*           msdb.S_SURVEY.T_LOCKING_TREE_LKT                                  *
*******************************************************************************
* INPUTS  :                                                                   *
*    @DURATION_S_ALERT (default 30) blocking time treshold to alert           *
*    @BLOCKED_COUNT_ALERT (default 5) blocked session count treshold to alert *
*    @BLOCKED_DEEP_ALERT (default 3) blocking deep treshold to alert          *
*******************************************************************************
* EXAMPLE : EXEC msdb.S_SURVEY.P_SQLPRO_TREEBLOKERS                           *
******************************************************************************/
SET NOCOUNT ON;

-- is there any blocking/blocked sessions ?
WHILE NOT EXISTS(SELECT *
                 FROM   sys.dm_exec_requests
                 WHERE  blocking_session_id > 0)
BEGIN
   -- if not wait and see
   WAITFOR DELAY '00:00:01';
   CONTINUE;
END;

-- if yes, tracks it, first store all informations into a table
DECLARE @GUID UNIQUEIDENTIFIER = NEWID(); -- with a session identifier
DECLARE @AUDIT_QUERY NVARCHAR(256);
DECLARE @T TABLE 
(session_id                     SMALLINT ,
 blocking_session_id            SMALLINT,
 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),
 capture_time                   DATETIME
);
INSERT INTO @T
SELECT s.session_id, 
       COALESCE(r.blocking_session_id, 0) AS blocking_session_id,
       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, 
       SYSDATETIME()
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;

-- synthese of all the information
WITH 
BLK (session_id, blocking_session_id, LEVEL, BLK_LEVEL, BLK_COUNT, CHAIN_ID)
AS 
(
SELECT session_id, blocking_session_id, 
       CAST(STR(session_id, 5) AS VARCHAR(2000)) AS LEVEL,
       0 AS BLK_LEVEL, 0 AS BLK_COUNT, 
       ROW_NUMBER() OVER(ORDER BY session_id) AS CHAIN_ID
FROM   @T R
WHERE  (blocking_session_id = 0 OR 
        blocking_session_id = session_id ) 
        AND EXISTS (SELECT *
                    FROM   @T AS R2
                    WHERE  R2.blocking_session_id = R.session_id AND
                           R2.blocking_session_id <> R2.session_id )
UNION  ALL
SELECT R.session_id, R.blocking_session_id,
       CAST(BLK.LEVEL + STR(R.session_id, 5) AS VARCHAR(2000)) AS LEVEL,
       BLK_LEVEL + 1, 
       BLK_COUNT + COUNT(*) OVER(PARTITION BY R.blocking_session_id), CHAIN_ID
FROM   @T AS R
       INNER JOIN BLK 
          ON R.blocking_session_id = BLK.session_id
WHERE  R.blocking_session_id > 0 AND 
       R.blocking_session_id <> R.session_id 
)
INSERT INTO msdb.S_SURVEY.T_LOCKING_TREE_LKT
SELECT @GUID,
       SYSUTCDATETIME() AS TRACKING_UTC_TIME,
       capture_time AS TRACKING_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.session_id, 5) AS BLOCKING_TREE,
       T.session_id,
       T.blocking_session_id AS blocker_session_id,
       DB_NAME(T.database_id) AS DATATASE_NAME,
       CASE BLK_LEVEL 
             WHEN 0 THEN CONCAT('KILL ', T.session_id, ';') 
             ELSE '' 
          END AS KILL_TO_UNBLOCK,
       T.status,
       T.command,
       T.last_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(COALESCE(T.TSQL_text, q.text) ,
                      T.statement_start_offset/2 + 1,  
                      (CASE T.statement_end_offset   
                            WHEN -1 THEN DATALENGTH(COALESCE(T.TSQL_text, q.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.session_id = T.session_id
       LEFT OUTER JOIN sys.dm_exec_connections AS c
          ON T.blocking_session_id = c.session_id AND T.TSQL_text IS NULL
          OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS q
ORDER BY TOTAL_BLOCKED, CHAIN_ID, BLOCK_DEEP, last_request_start_time;

-- perhaps nothing to do !
IF @@ROWCOUNT = 0
   RETURN;

-- deleting old rows
DELETE FROM msdb.S_SURVEY.T_LOCKING_TREE_LKT
WHERE  DATEDIFF(day, LKT_TRACKING_UTC_TIME, SYSUTCDATETIME()) > @DELETE_DEEP_DAYS;

-- informs if criteria has been reached
IF EXISTS(SELECT *
          FROM   msdb.S_SURVEY.T_LOCKING_TREE_LKT
          WHERE  LKT_SESSION = @GUID
            AND  (DATEDIFF(s, LKT_LAST_QUERY_START, LKT_TRACKING_TIME) >= @DURATION_S_ALERT
                  OR LKT_TOTAL_BLOCKED >= @BLOCKED_COUNT_ALERT
                  OR LKT_BLOCK_DEEP >= @BLOCKED_DEEP_ALERT))
BEGIN
   SET @AUDIT_QUERY = 'SELECT * FROM msdb.S_SURVEY.FT_LOCKING_TREE_LKT(''' + CAST(@GUID AS VARCHAR(38)) + ''');';
   IF @@LANGUAGE = 'Français'
      RAISERROR('Chaine de blocage détectée. Lancez la requête suivante pour audit : "%s".', 16, 1)
   ELSE
      RAISERROR('Blocking chain detected. Run the following query for audit : "%s".', 16, 1)
END;

GO