CREATE TABLE [srv].[SQLQuery](
 [SQLHandle] [varbinary](64) NOT NULL,
 [TSQL] [nvarchar](max) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SQLQuery] PRIMARY KEY CLUSTERED 
(
 [SQLHandle] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[SQLQuery] ADD  CONSTRAINT [DF_SQLQuery_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE TABLE [srv].[PlanQuery](
 [PlanHandle] [varbinary](64) NOT NULL,
 [SQLHandle] [varbinary](64) NOT NULL,
 [QueryPlan] [xml] NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PlanQuery] PRIMARY KEY CLUSTERED 
(
 [SQLHandle] ASC,
 [PlanHandle] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[PlanQuery] ADD  CONSTRAINT [DF_PlanQuery_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE TABLE [srv].[RequestStatistics](
 [session_id] [smallint] NOT NULL,
 [blocker] [int] NULL,
 [Status] [nvarchar](30) NOT NULL,
 [start_time] [datetime] NOT NULL,
 [percent_complete] [real] NULL,
 [totalElapsedTime] [int] NOT NULL,
 [TotalCPU] [int] NOT NULL,
 [memory_usage_kb] [int] NULL,
 [row_count] [bigint] NOT NULL,
 [totalReads] [bigint] NOT NULL,
 [totalLogicalReads] [bigint] NULL,
 [totalWrites] [bigint] NOT NULL,
 [Writes_in_tempdb] [bigint] NULL,
 [text] [xml] NULL,
 [DBName] [nvarchar](128) NULL,
 [ObjName] [nvarchar](128) NULL,
 [Wait_type] [nvarchar](60) NULL,
 [wait_resource] [nvarchar](256) NOT NULL,
 [Login_name] [nvarchar](128) NOT NULL,
 [Host_name] [nvarchar](128) NULL,
 [Blocking_text] [xml] NULL,
 [sql_handle] [varbinary](64) NULL,
 [plan_handle] [varbinary](64) NULL,
 [request_id] [int] NULL,
 [database_id] [smallint] NULL,
 [user_id] [int] NULL,
 [connection_id] [uniqueidentifier] NULL,
 [transaction_id] [bigint] NULL,
 [command] [nvarchar](32) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 [EndRegUTCDate] [datetime] NULL,
 [LevelDetail] [nvarchar](max) NULL,
 [IsNotFinishBlockingSession] [bit] NULL,
 [IsRootBlocker] [bit] NULL,
 [RootBlockerSession_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[RequestStatistics] ADD  CONSTRAINT [DF_RequestStatistics_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE TABLE [srv].[RequestBlocked](
 [session_id] [smallint] NOT NULL,
 [blocker] [int] NULL,
 [Status] [nvarchar](30) NOT NULL,
 [start_time] [datetime] NOT NULL,
 [percent_complete] [real] NULL,
 [totalElapsedTime] [int] NOT NULL,
 [TotalCPU] [int] NOT NULL,
 [memory_usage_kb] [int] NULL,
 [row_count] [bigint] NOT NULL,
 [totalReads] [bigint] NOT NULL,
 [totalLogicalReads] [bigint] NULL,
 [totalWrites] [bigint] NOT NULL,
 [Writes_in_tempdb] [bigint] NULL,
 [text] [xml] NULL,
 [DBName] [nvarchar](128) NULL,
 [ObjName] [nvarchar](128) NULL,
 [Wait_type] [nvarchar](60) NULL,
 [wait_resource] [nvarchar](256) NOT NULL,
 [Login_name] [nvarchar](128) NOT NULL,
 [Host_name] [nvarchar](128) NULL,
 [Blocking_text] [xml] NULL,
 [sql_handle] [varbinary](64) NULL,
 [plan_handle] [varbinary](64) NULL,
 [request_id] [int] NULL,
 [database_id] [smallint] NULL,
 [user_id] [int] NULL,
 [connection_id] [uniqueidentifier] NULL,
 [transaction_id] [bigint] NULL,
 [command] [nvarchar](32) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 [Count] [int] NOT NULL,
 [LevelDetail] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[RequestBlocked] ADD  CONSTRAINT [DF_RequestBlocked_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO
ALTER TABLE [srv].[RequestBlocked] ADD  CONSTRAINT [DF_RequestBlocked_Count]  DEFAULT ((1)) FOR [Count];
GO

CREATE TABLE [srv].[RequestBlockedItBlocks](
 [session_id] [smallint] NOT NULL,
 [blocker] [int] NULL,
 [Status] [nvarchar](30) NOT NULL,
 [start_time] [datetime] NOT NULL,
 [percent_complete] [real] NULL,
 [totalElapsedTime] [int] NOT NULL,
 [TotalCPU] [int] NOT NULL,
 [memory_usage_kb] [int] NULL,
 [row_count] [bigint] NOT NULL,
 [totalReads] [bigint] NOT NULL,
 [totalLogicalReads] [bigint] NULL,
 [totalWrites] [bigint] NOT NULL,
 [Writes_in_tempdb] [bigint] NULL,
 [text] [xml] NULL,
 [DBName] [nvarchar](128) NULL,
 [ObjName] [nvarchar](128) NULL,
 [Wait_type] [nvarchar](60) NULL,
 [wait_resource] [nvarchar](256) NOT NULL,
 [Login_name] [nvarchar](128) NOT NULL,
 [Host_name] [nvarchar](128) NULL,
 [Blocking_text] [xml] NULL,
 [sql_handle] [varbinary](64) NULL,
 [plan_handle] [varbinary](64) NULL,
 [request_id] [int] NULL,
 [database_id] [smallint] NULL,
 [user_id] [int] NULL,
 [connection_id] [uniqueidentifier] NULL,
 [transaction_id] [bigint] NULL,
 [command] [nvarchar](32) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 [LevelDetail] [nvarchar](max) NULL,
 [RootBlockerSession_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[RequestBlockedItBlocks] ADD  CONSTRAINT [DF_RequestBlockedItBlocks_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE TABLE [srv].[RequestBlockedKilled](
 [session_id] [smallint] NOT NULL,
 [blocker] [int] NULL,
 [Status] [nvarchar](30) NOT NULL,
 [start_time] [datetime] NOT NULL,
 [percent_complete] [real] NULL,
 [totalElapsedTime] [int] NOT NULL,
 [TotalCPU] [int] NOT NULL,
 [memory_usage_kb] [int] NULL,
 [row_count] [bigint] NOT NULL,
 [totalReads] [bigint] NOT NULL,
 [totalLogicalReads] [bigint] NULL,
 [totalWrites] [bigint] NOT NULL,
 [Writes_in_tempdb] [bigint] NULL,
 [text] [xml] NULL,
 [DBName] [nvarchar](128) NULL,
 [ObjName] [nvarchar](128) NULL,
 [Wait_type] [nvarchar](60) NULL,
 [wait_resource] [nvarchar](256) NOT NULL,
 [Login_name] [nvarchar](128) NOT NULL,
 [Host_name] [nvarchar](128) NULL,
 [Blocking_text] [xml] NULL,
 [sql_handle] [varbinary](64) NULL,
 [plan_handle] [varbinary](64) NULL,
 [request_id] [int] NULL,
 [database_id] [smallint] NULL,
 [user_id] [int] NULL,
 [connection_id] [uniqueidentifier] NULL,
 [transaction_id] [bigint] NULL,
 [command] [nvarchar](32) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 [LevelDetail] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[RequestBlockedKilled] ADD  CONSTRAINT [DF_RequestBlockedKilled_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE TABLE [srv].[RequestBlockedItBlocksArchive](
 [session_id] [smallint] NOT NULL,
 [blocker] [int] NULL,
 [Status] [nvarchar](30) NOT NULL,
 [start_time] [datetime] NOT NULL,
 [percent_complete] [real] NULL,
 [totalElapsedTime] [int] NOT NULL,
 [TotalCPU] [int] NOT NULL,
 [memory_usage_kb] [int] NULL,
 [row_count] [bigint] NOT NULL,
 [totalReads] [bigint] NOT NULL,
 [totalLogicalReads] [bigint] NULL,
 [totalWrites] [bigint] NOT NULL,
 [Writes_in_tempdb] [bigint] NULL,
 [text] [xml] NULL,
 [DBName] [nvarchar](128) NULL,
 [ObjName] [nvarchar](128) NULL,
 [Wait_type] [nvarchar](60) NULL,
 [wait_resource] [nvarchar](256) NOT NULL,
 [Login_name] [nvarchar](128) NOT NULL,
 [Host_name] [nvarchar](128) NULL,
 [Blocking_text] [xml] NULL,
 [sql_handle] [varbinary](64) NULL,
 [plan_handle] [varbinary](64) NULL,
 [request_id] [int] NULL,
 [database_id] [smallint] NULL,
 [user_id] [int] NULL,
 [connection_id] [uniqueidentifier] NULL,
 [transaction_id] [bigint] NULL,
 [command] [nvarchar](32) NULL,
 [InsertUTCDate] [datetime] NOT NULL,
 [LevelDetail] [nvarchar](max) NULL,
 [RootBlockerSession_ID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO
ALTER TABLE [srv].[RequestBlockedItBlocksArchive] ADD  CONSTRAINT [DF_RequestBlockedItBlocksArchive_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate];
GO

CREATE VIEW [inf].[vRequestDetail] AS
SELECT x.session_id                                                        session_id,
       COALESCE(x.blocking_session_id, 0)                                  blocker,
       x.Status                                                            Status,
       x.Start_time                                                        Start_time,
       x.percent_complete,
       x.totalElapsedTime                                                  totalElapsedTime,
       x.TotalCPU                                                          TotalCPU,
       x.memory_usage * 8                                                  memory_usage_kb,
       --x.granted_query_memory * 8                                          granted_query_memory_kb,
       x.row_count                                                         row_count,
       x.totalReads                                                        totalReads,
       x.totalLogicalReads                                                 totalLogicalReads,
       x.totalWrites                                                       totalWrites,
       x.Writes_in_tempdb                                                  Writes_in_tempdb,
       -- --
       (SELECT SUBSTRING(text,
                         x.statement_start_offset / 2,
                         (CASE
                            WHEN x.statement_end_offset = -1 THEN
                              1000000
                            ELSE
                              x.statement_end_offset
                            END
                          -
                          x.statement_start_offset) / 2)
          FROM sys.dm_exec_sql_text(x.sql_handle) FOR XML PATH(''), TYPE ) "text",
       (select top(1) text from sys.dm_exec_sql_text(x.[sql_handle])) as [TSQL],
       -- --
       db_name(x.database_id)                                              DBName,
       -- --
       (SELECT object_name(objectid)
          FROM sys.dm_exec_sql_text(x.sql_handle))                         ObjName,
       -- --
       x.Wait_type                                                         Wait_type,
       x.wait_resource                                                     wait_resource,
       x.Login_name                                                        Login_name,
       x.Host_name                                                         Host_name,
       (SELECT p.text
          FROM (SELECT MIN(sql_handle) AS sql_handle
                  FROM sys.dm_exec_requests r2
                  WHERE r2.session_id = x.blocking_session_id) AS r_blocking
               -- --
               CROSS APPLY
               -- --
               (SELECT SUBSTRING(text,
                                 x.statement_start_offset / 2,
                                 (CASE
                                    WHEN x.statement_end_offset = -1 THEN
                                      LEN(CONVERT(nvarchar(max), text)) * 2
                                    ELSE
                                      x.statement_end_offset
                                    END
                                 -
                                x.statement_start_offset) / 2)
                  FROM sys.dm_exec_sql_text(r_blocking.sql_handle) FOR XML PATH(''), TYPE ) p (text))        AS Blocking_text,
       (select top(1) [query_plan] from sys.dm_exec_query_plan(x.plan_handle)) as [QueryPlan],
       x.[sql_handle]                                                      [sql_handle],
       x.plan_handle                                                       plan_handle,
       x.request_id,
       x.[user_id],
       x.connection_id,
       x.transaction_id,
       x.command,
       x.database_id
       --into srv.RequestStatistics
  FROM (SELECT r.session_id,
               s.host_name,
               s.login_name,
               s.memory_usage,
               r.start_time,
               r.sql_handle,
               r.database_id,
               r.blocking_session_id,
               r.wait_type,
               r.status,
               r.statement_start_offset,
               r.statement_end_offset,
               r.total_elapsed_time as totalElapsedTime,
               r.reads AS totalReads,
               cast(r.logical_reads as bigint) as totalLogicalReads,
               r.writes AS totalWrites,
               r.cpu_time AS totalCPU,
               r.granted_query_memory,
               r.row_count,
               r.wait_resource,
               r.scheduler_id,
               r.plan_handle,
               r.request_id,
               r.[user_id],
               r.connection_id,
               r.transaction_id,
               r.command,
               sum(tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count) AS writes_in_tempdb,
               min(r.percent_complete) as percent_complete
          FROM sys.dm_exec_requests r 
               JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
               JOIN sys.dm_db_task_space_usage tsu ON s.session_id =tsu.session_id and r.request_id = tsu.request_id
          WHERE r.status IN ('running', 'runnable', 'suspended','rollback')
          GROUP BY r.session_id,         
                   s.host_name,         
                   s.login_name,         
                   s.memory_usage,
                   r.start_time,         
                   r.sql_handle, 
                   r.plan_handle,
                   r.database_id,
                   r.request_id,
                   r.blocking_session_id,  
                   r.wait_type,  
                   r.status,  
                   r.statement_start_offset,
                   r.statement_end_offset,  
                   r.total_elapsed_time,         
                   r.reads,         
                   cast(r.logical_reads as bigint),         
                   r.writes,
                   r.cpu_time,  
                   r.granted_query_memory,         
                   r.row_count,
                   r.wait_resource,
                   r.scheduler_id,
                   r.connection_id,
                   r.transaction_id,
                   r.command,
                   r.[user_id]) x
  WHERE x.session_id <> @@spid;

CREATE VIEW [srv].[vRequestStatistics] AS
SELECT RS.[session_id]
      ,RS.[blocker]
      ,RS.[Status]
      ,RS.[start_time]
      ,RS.[percent_complete]
      ,RS.[totalElapsedTime]
      ,RS.[TotalCPU]
      ,RS.[memory_usage_kb]
      ,RS.[row_count]
      ,RS.[totalReads]
      ,RS.[totalLogicalReads]
      ,RS.[totalWrites]
      ,RS.[Writes_in_tempdb]
      ,RS.[text]
      ,RS.[DBName]
      ,RS.[ObjName]
      ,RS.[Wait_type]
      ,RS.[wait_resource]
      ,RS.[Login_name]
      ,RS.[Host_name]
      ,RS.[Blocking_text]
      ,PQ.[QueryPlan]
      ,RS.[sql_handle]
      ,RS.[plan_handle]
      ,RS.[request_id]
      ,RS.[database_id]
      ,RS.[user_id]
      ,RS.[connection_id]
      ,RS.[transaction_id]
      ,RS.[command]
      ,RS.[InsertUTCDate]
      ,RS.[EndRegUTCDate]
      ,SQ.[TSQL]
      ,RS.RootBlockerSession_ID
      ,RS.IsNotFinishBlockingSession
      ,RS.[LevelDetail]
  FROM [srv].[RequestStatistics] AS RS WITH(NOLOCK)
  LEFT JOIN [srv].[SQLQuery]     AS SQ WITH(NOLOCK) ON SQ.[SQLHandle]   = RS.[sql_handle]
  LEFT JOIN [srv].[PlanQuery]    AS PQ WITH(NOLOCK) ON PQ.[SQLHandle]   = RS.[sql_handle]
                                                    AND PQ.[PlanHandle] = RS.[plan_handle];

CREATE VIEW [srv].[vRequestBlocked] AS
SELECT RS.[session_id]
      ,RS.[blocker]
      ,RS.[Status]
      ,RS.[start_time]
      ,RS.[percent_complete]
      ,RS.[totalElapsedTime]
      ,RS.[TotalCPU]
      ,RS.[memory_usage_kb]
      ,RS.[row_count]
      ,RS.[totalReads]
      ,RS.[totalLogicalReads]
      ,RS.[totalWrites]
      ,RS.[Writes_in_tempdb]
      ,RS.[text]
      ,RS.[DBName]
      ,RS.[ObjName]
      ,RS.[Wait_type]
      ,RS.[wait_resource]
      ,RS.[Login_name]
      ,RS.[Host_name]
      ,RS.[Blocking_text]
      ,PQ.[QueryPlan]
      ,RS.[sql_handle]
      ,RS.[plan_handle]
      ,RS.[request_id]
      ,RS.[database_id]
      ,RS.[user_id]
      ,RS.[connection_id]
      ,RS.[transaction_id]
      ,RS.[command]
      ,RS.[InsertUTCDate]
      ,SQ.[TSQL]
      ,RS.[LevelDetail]
      ,RS.[Count]
  FROM [srv].[RequestBlocked]       AS RS WITH(NOLOCK)
  LEFT JOIN [srv].[SQLQuery]        AS SQ WITH(NOLOCK) ON SQ.[SQLHandle]   = RS.[sql_handle]
  LEFT JOIN [srv].[PlanQuery]       AS PQ WITH(NOLOCK) ON PQ.[SQLHandle]   = RS.[sql_handle]
                                                       AND PQ.[PlanHandle] = RS.[plan_handle];

CREATE   VIEW [srv].[vRequestBlockedItBlocks] AS
SELECT RS.[session_id]
      ,RS.[blocker]
      ,RS.[Status]
      ,RS.[start_time]
      ,RS.[percent_complete]
      ,RS.[totalElapsedTime]
      ,RS.[TotalCPU]
      ,RS.[memory_usage_kb]
      ,RS.[row_count]
      ,RS.[totalReads]
      ,RS.[totalLogicalReads]
      ,RS.[totalWrites]
      ,RS.[Writes_in_tempdb]
      ,RS.[text]
      ,RS.[DBName]
      ,RS.[ObjName]
      ,RS.[Wait_type]
      ,RS.[wait_resource]
      ,RS.[Login_name]
      ,RS.[Host_name]
      ,RS.[Blocking_text]
      ,PQ.[QueryPlan]
      ,RS.[sql_handle]
      ,RS.[plan_handle]
      ,RS.[request_id]
      ,RS.[database_id]
      ,RS.[user_id]
      ,RS.[connection_id]
      ,RS.[transaction_id]
      ,RS.[command]
      ,RS.[InsertUTCDate]
      ,SQ.[TSQL]
      ,RS.[LevelDetail]
  FROM [srv].[RequestBlockedItBlocks]       AS RS WITH(NOLOCK)
  LEFT JOIN [srv].[SQLQuery]                AS SQ WITH(NOLOCK) ON SQ.[SQLHandle]   = RS.[sql_handle]
  LEFT JOIN [srv].[PlanQuery]               AS PQ WITH(NOLOCK) ON PQ.[SQLHandle]   = RS.[sql_handle]
                                                               AND PQ.[PlanHandle] = RS.[plan_handle];

CREATE   VIEW [srv].[vRequestBlockedItBlocksArchive] AS
SELECT RS.[session_id]
      ,RS.[blocker]
      ,RS.[Status]
      ,RS.[start_time]
      ,RS.[percent_complete]
      ,RS.[totalElapsedTime]
      ,RS.[TotalCPU]
      ,RS.[memory_usage_kb]
      ,RS.[row_count]
      ,RS.[totalReads]
      ,RS.[totalLogicalReads]
      ,RS.[totalWrites]
      ,RS.[Writes_in_tempdb]
      ,RS.[text]
      ,RS.[DBName]
      ,RS.[ObjName]
      ,RS.[Wait_type]
      ,RS.[wait_resource]
      ,RS.[Login_name]
      ,RS.[Host_name]
      ,RS.[Blocking_text]
      ,PQ.[QueryPlan]
      ,RS.[sql_handle]
      ,RS.[plan_handle]
      ,RS.[request_id]
      ,RS.[database_id]
      ,RS.[user_id]
      ,RS.[connection_id]
      ,RS.[transaction_id]
      ,RS.[command]
      ,RS.[InsertUTCDate]
      ,SQ.[TSQL]
      ,RS.[LevelDetail]
  FROM [srv].[RequestBlockedItBlocksArchive]       AS RS WITH(NOLOCK)
  LEFT JOIN [srv].[SQLQuery]                       AS SQ WITH(NOLOCK) ON SQ.[SQLHandle]   = RS.[sql_handle]
  LEFT JOIN [srv].[PlanQuery]                      AS PQ WITH(NOLOCK) ON PQ.[SQLHandle]   = RS.[sql_handle]
                                                                      AND PQ.[PlanHandle] = RS.[plan_handle];

CREATE   VIEW [srv].[vRequestBlockedKilled] AS
SELECT RS.[session_id]
      ,RS.[blocker]
      ,RS.[Status]
      ,RS.[start_time]
      ,RS.[percent_complete]
      ,RS.[totalElapsedTime]
      ,RS.[TotalCPU]
      ,RS.[memory_usage_kb]
      ,RS.[row_count]
      ,RS.[totalReads]
      ,RS.[totalLogicalReads]
      ,RS.[totalWrites]
      ,RS.[Writes_in_tempdb]
      ,RS.[text]
      ,RS.[DBName]
      ,RS.[ObjName]
      ,RS.[Wait_type]
      ,RS.[wait_resource]
      ,RS.[Login_name]
      ,RS.[Host_name]
      ,RS.[Blocking_text]
      ,PQ.[QueryPlan]
      ,RS.[sql_handle]
      ,RS.[plan_handle]
      ,RS.[request_id]
      ,RS.[database_id]
      ,RS.[user_id]
      ,RS.[connection_id]
      ,RS.[transaction_id]
      ,RS.[command]
      ,RS.[InsertUTCDate]
      ,SQ.[TSQL]
      ,RS.[LevelDetail]
  FROM [srv].[RequestBlockedKilled] AS RS WITH(NOLOCK)
  LEFT JOIN [srv].[SQLQuery]        AS SQ WITH(NOLOCK) ON SQ.[SQLHandle]   = RS.[sql_handle]
  LEFT JOIN [srv].[PlanQuery]       AS PQ WITH(NOLOCK) ON PQ.[SQLHandle]   = RS.[sql_handle]
                                                       AND PQ.[PlanHandle] = RS.[plan_handle];

[
  {
    "Level": 2,
    "LevelDetail": [
      195,
      91,
      245
    ]
  },
  {
    "Level": 1,
    "LevelDetail": [
      194,
      245
    ]
  },
  {
    "Level": 1,
    "LevelDetail": [
      187,
      245
    ]
  },
  {
    "Level": 1,
    "LevelDetail": [
      113,
      245
    ]
  },
  {
    "Level": 3,
    "LevelDetail": [
      112,
      195,
      91,
      245
    ]
  },
  {
    "Level": 1,
    "LevelDetail": [
      99,
      245
    ]
  },
  {
    "Level": 1,
    "LevelDetail": [
      91,
      245
    ]
  }
]

DECLARE @JSON NVARCHAR (MAX) = '[{"Level":4,"LevelDetail":[17, 16, 14, 12, 10]},{"Level":2,"LevelDetail":[14, 12, 10]}]';
 
SELECT
    j.Level,
    l.LevelDetailValue,
    l.LevelDetailIndex
FROM OPENJSON(@JSON) WITH (
    Level INT,
    LevelDetail NVARCHAR(MAX) AS JSON
) j
CROSS APPLY (
    SELECT
        CAST(value AS INT) AS LevelDetailValue,
        CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT) - 1 AS LevelDetailIndex
    FROM OPENJSON(j.LevelDetail)
) l;

Результат:

То есть получили вывод длины цепочки в поле Level без учёта конечного текущего сеанса (N-1, где N — длина всей цепочки блокировок), идентификатор сеанса в поле LevelDetailValue и порядковый номер в цепочке блокировок в поле LevelDetailIndex

ALTER PROCEDURE [srv].[AutoStatisticsActiveRequests]
  @Count INT = 5 --кол-во раз попаданий сессии как блокирующей для остальных (сама при этом она не блокируется никем явно)
 ,@LoginName NVARCHAR(128) = '<login>' --под каким логином ищем блокированные сессии (если @LoginName = NULL, то фильтр по логину не будет включён)
 ,@DayOld INT = 30 --кол-во дней для архива запросов (кроме тех, кого автоудалили с деталями)
AS
BEGIN
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  /*
Так можно читать данные из поля `LevelDetail` в таблицах `srv.RequestStatistics`, `srv.RequestBlocked`, `srv.RequestBlockedItBlocks`, `srv.RequestBlockedItBlocksArchive` и `srv.RequestBlockedKilled`:
  DECLARE @JSON NVARCHAR (MAX) = '[{"Level":4,"LevelDetail":[17, 16, 14, 12, 10]},{"Level":2,"LevelDetail":[14, 12, 10]}]';

  SELECT 
      j.Level,
      l.LevelDetailValue,
      l.LevelDetailIndex
  FROM OPENJSON(@JSON) WITH (
      Level INT,
      LevelDetail NVARCHAR(MAX) AS JSON
  ) j
  CROSS APPLY (
      SELECT 
          CAST(value AS INT) AS LevelDetailValue,
          CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS INT) - 1 AS LevelDetailIndex
      FROM OPENJSON(j.LevelDetail)
  ) l;
  */

  DECLARE @DT DATETIME = GETUTCDATE();

 DECLARE @tbl0 TABLE (
      [SQLHandle] [varbinary](64) NOT NULL,
      [TSQL] [nvarchar](max) NULL
        );
 
 DECLARE @tbl1 TABLE (
      [PlanHandle] [varbinary](64) NOT NULL,
      [SQLHandle] [varbinary](64) NOT NULL,
      [QueryPlan] [xml] NULL
        );

 DECLARE @tbl2 TABLE (
       [session_id] [smallint] NOT NULL,
             [blocker] [int] NULL,
             [Status] [nvarchar](30) NOT NULL,
             [start_time] [datetime] NOT NULL,
             [percent_complete] [real] NULL,
             [totalElapsedTime] [int] NOT NULL,
             [TotalCPU] [int] NOT NULL,
             [memory_usage_kb] [int] NULL,
             [row_count] [bigint] NOT NULL,
             [totalReads] [bigint] NOT NULL,
             [totalLogicalReads] [bigint] NULL,
             [totalWrites] [bigint] NOT NULL,
             [Writes_in_tempdb] [bigint] NULL,
             [text] [xml] NULL,
             [DBName] [nvarchar](128) NULL,
             [ObjName] [nvarchar](128) NULL,
             [Wait_type] [nvarchar](60) NULL,
             [wait_resource] [nvarchar](256) NOT NULL,
             [Login_name] [nvarchar](128) NOT NULL,
             [Host_name] [nvarchar](128) NULL,
             [Blocking_text] [xml] NULL,
             [QueryPlan] [xml] NULL,
             [sql_handle] [varbinary](64) NULL,
             [plan_handle] [varbinary](64) NULL,
             [request_id] [int] NULL,
             [database_id] [smallint] NULL,
             [user_id] [int] NULL,
             [connection_id] [uniqueidentifier] NULL,
             [transaction_id] [bigint] NULL,
             [command] [nvarchar](32) NULL,
              [TSQL] [nvarchar](max) NULL
      );

  DECLARE @tbl3 TABLE (
       [session_id] [smallint] NOT NULL,
             [blocker] [int] NULL,
              [start_time] [datetime] NOT NULL,
             [Login_name] [nvarchar](128) NULL,
              IsNotFinishBlockingSession BIT NOT NULL
      );

  DECLARE @tbl_ListDetail TABLE (
                                  [session_id] INT NOT NULL PRIMARY KEY
                                 ,[Login_name] [nvarchar](128) NULL
                                 ,[start_time] [datetime] NOT NULL
                                 ,LevelDetail NVARCHAR(MAX) NOT NULL
                                 ,IsNotFinishBlockingSession BIT NOT NULL
                                 ,IsRootBlocker BIT NOT NULL
                                 ,RootBlockerSession_ID INT NULL
                                );

  DECLARE @tbl_blockers TABLE (
                                [session_id] INT NOT NULL PRIMARY KEY,
                                LevelDetail NVARCHAR(MAX) NOT NULL,
                                [Login_name] [nvarchar](128) NULL,
                                [start_time] [datetime] NOT NULL
                              );

  DECLARE @tbl_blockeds TABLE (
                                [session_id] INT NOT NULL PRIMARY KEY,
                                LevelDetail NVARCHAR(MAX) NOT NULL,
                                [Login_name] [nvarchar](128) NULL,
                                RootBlockerSession_ID INT NULL,
                                [start_time] [datetime] NOT NULL
                               );

  IF (@Count  < 3)           SET @Count     = 3;
  IF (@DayOld < 3)           SET @DayOld    = 3;
  IF (TRIM(@LoginName) = '') SET @LoginName = NULL;

 INSERT INTO @tbl2 (
      [session_id]
            ,[blocker]
            ,[Status]
            ,[start_time]
            ,[percent_complete]
            ,[totalElapsedTime]
            ,[TotalCPU]
            ,[memory_usage_kb]
            ,[row_count]
            ,[totalReads]
            ,[totalLogicalReads]
            ,[totalWrites]
            ,[Writes_in_tempdb]
            ,[text]
            ,[DBName]
            ,[ObjName]
            ,[Wait_type]
            ,[wait_resource]
            ,[Login_name]
            ,[Host_name]
            ,[Blocking_text]
            ,[QueryPlan]
            ,[sql_handle]
            ,[plan_handle]
            ,[request_id]
            ,[database_id]
            ,[user_id]
            ,[connection_id]
            ,[transaction_id]
            ,[command]
            ,[TSQL]
       )
 SELECT [session_id]
      ,[blocker]
      ,[Status]
      ,[start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[QueryPlan]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[database_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,[TSQL]
  FROM [inf].[vRequestDetail];

 INSERT INTO @tbl1 (
      [PlanHandle],
      [SQLHandle],
      [QueryPlan]
       )
 SELECT    [plan_handle],
      [sql_handle],
      (SELECT TOP(1) [query_plan] FROM sys.dm_exec_query_plan([plan_handle])) AS [QueryPlan]
 FROM @tbl2
 WHERE (SELECT TOP(1) [query_plan] FROM sys.dm_exec_query_plan([plan_handle])) IS NOT NULL
 GROUP BY [plan_handle],
    [sql_handle];

 INSERT INTO @tbl0 (
      [SQLHandle],
      [TSQL]
       )
 SELECT    [sql_handle],
      (SELECT TOP(1) text FROM sys.dm_exec_sql_text([sql_handle])) AS [TSQL]
 FROM @tbl2
 WHERE (SELECT TOP(1) text FROM sys.dm_exec_sql_text([sql_handle])) IS NOT NULL
 GROUP BY [sql_handle];
 
 ;MERGE [srv].[SQLQuery] AS trg
 USING @tbl0 AS src ON trg.[SQLHandle]=src.[SQLHandle]
 WHEN NOT MATCHED BY TARGET THEN
 INSERT (
    [SQLHandle],
    [TSQL]
     )
 VALUES (
    src.[SQLHandle],
    src.[TSQL]
     );
 
 ;MERGE [srv].[PlanQuery] AS trg
 USING @tbl1 AS src ON trg.[SQLHandle]=src.[SQLHandle] AND trg.[PlanHandle]=src.[PlanHandle]
 WHEN NOT MATCHED BY TARGET THEN
 INSERT (
    [PlanHandle],
    [SQLHandle],
    [QueryPlan]
     )
 VALUES (
   src.[PlanHandle],
    src.[SQLHandle],
    src.[QueryPlan]
     );

  INSERT INTO @tbl3 ([session_id], blocker, Login_name, IsNotFinishBlockingSession, [start_time])
  SELECT t.[session_id]
       , t.blocker
       , t.Login_name
       , 0 AS IsNotFinishBlockingSession
       , t.[start_time]
  FROM @tbl2 as t
  --добавляем недостающие ид сессий, которые блокируют другие сессии в полученном снимке активных сессий
  UNION ALL
  SELECT t.blocker AS [session_id]
       , 0 AS blocker
       , NULL AS Login_name
       , 1 AS IsNotFinishBlockingSession
       , GETDATE() AS [start_time]
  FROM @tbl2 as t
  WHERE NOT EXISTS (SELECT 1 FROM @tbl2 AS t0 WHERE t0.[session_id] = t.blocker)
    AND t.blocker > 0
  GROUP BY t.blocker

  ;WITH BlockingChain AS (
      SELECT 
          rbi.[session_id],
          rbi.blocker,
          rbi.Login_name,
          rbi.[start_time],
          0 AS [level],
          CAST(rbi.[session_id] AS NVARCHAR(MAX)) AS LevelDetail,
          CAST(',' + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',' AS NVARCHAR(MAX)) AS Visited,
          rbi.IsNotFinishBlockingSession,
          rbi.[session_id] AS RootBlockerSession_ID
      FROM @tbl3 rbi
      WHERE rbi.blocker <= 0
      UNION ALL
      SELECT 
          rbi.[session_id],
          rbi.blocker,
          rbi.Login_name,
          rbi.[start_time],
          bc.[level] + 1,
          CONCAT(LevelDetail, ',', rbi.[session_id]) AS LevelDetail,
          CAST(bc.Visited + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',' AS NVARCHAR(MAX)) AS Visited,
          CASE
            WHEN (bc.IsNotFinishBlockingSession = 1) OR (rbi.IsNotFinishBlockingSession = 1)
              THEN CAST(1 AS BIT)
            ELSE CAST(0 AS BIT)
          END AS IsNotFinishBlockingSession,
          bc.RootBlockerSession_ID AS RootBlockerSession_ID
      FROM BlockingChain bc
      INNER JOIN @tbl3 rbi
          ON rbi.blocker = bc.[session_id]
         AND bc.[session_id] <> rbi.[session_id]
      WHERE CHARINDEX(',' + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',', bc.Visited) = 0
  )
  INSERT INTO @tbl_ListDetail([session_id], [Login_name], LevelDetail, IsNotFinishBlockingSession, IsRootBlocker, RootBlockerSession_ID, [start_time])
  SELECT bc.[session_id], bc.[Login_name],
  (
    SELECT [Level]
         , JSON_QUERY('[' + REPLACE(LevelDetail, ',', ', ') + ']') AS LevelDetail
    FROM BlockingChain bc0
    WHERE bc0.[session_id] = bc.[session_id]
    FOR JSON PATH
  ) AS LevelDetail
  ,bc.IsNotFinishBlockingSession
  ,0 AS IsRootBlocker
  ,bc.RootBlockerSession_ID
  ,bc.[start_time]
  FROM BlockingChain bc
  WHERE bc.blocker > 0
  GROUP BY bc.[session_id], bc.[Login_name], bc.IsNotFinishBlockingSession, bc.RootBlockerSession_ID, bc.[start_time]
  OPTION (MAXRECURSION 10000);

  ;WITH BlockingChain AS (
      SELECT 
          rbi.[session_id],
          rbi.blocker,
          rbi.Login_name,
          rbi.[start_time],
          0 AS [level],
          CAST(rbi.[session_id] AS NVARCHAR(MAX)) AS LevelDetail,
          CAST(',' + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',' AS NVARCHAR(MAX)) AS Visited
      FROM @tbl3 rbi
      WHERE rbi.blocker > 0
      UNION ALL
      SELECT 
          rbi.[session_id],
          rbi.blocker,
          rbi.Login_name,
          rbi.[start_time],
          bc.[level] + 1,
          CONCAT(LevelDetail, ',', rbi.[session_id]) AS LevelDetail,
          CAST(bc.Visited + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',' AS NVARCHAR(MAX)) AS Visited
      FROM BlockingChain bc
      INNER JOIN @tbl3 rbi
          ON bc.blocker = rbi.[session_id]
         AND bc.[session_id] <> rbi.[session_id]
      WHERE CHARINDEX(',' + CAST(rbi.[session_id] AS NVARCHAR(MAX)) + ',', bc.Visited) = 0
  )
  INSERT INTO @tbl_ListDetail([session_id], [Login_name], LevelDetail, t.IsNotFinishBlockingSession, IsRootBlocker, [start_time])
  SELECT bc.[session_id], bc.[Login_name],
  (
    SELECT [Level]
         , JSON_QUERY('[' + REPLACE(LevelDetail, ',', ', ') + ']') AS LevelDetail
    FROM BlockingChain bc0
    WHERE bc0.[session_id] = bc.[session_id]
    FOR JSON PATH
  ) AS LevelDetail
  ,t.IsNotFinishBlockingSession
  ,1 AS IsRootBlocker
  ,bc.[start_time]
  FROM BlockingChain bc
  JOIN @tbl3 AS t ON t.[session_id] = bc.[session_id]
  WHERE bc.blocker <= 0
  GROUP BY bc.[session_id], bc.[Login_name], t.IsNotFinishBlockingSession, bc.[start_time]
  OPTION (MAXRECURSION 10000);

 UPDATE trg
 SET
  [blocker]               =src.[blocker]
  ,[Status]               =src.[Status]
  ,[percent_complete]     =src.[percent_complete]
  ,[totalElapsedTime]     =COALESCE(src.[totalElapsedTime]  , trg.[totalElapsedTime]  )
  ,[TotalCPU]             =COALESCE(src.[TotalCPU]          , trg.[TotalCPU]          )
  ,[memory_usage_kb]      =COALESCE(src.[memory_usage_kb]   , trg.[memory_usage_kb]   )
  ,[row_count]            =COALESCE(src.[row_count]         , trg.[row_count]         )
  ,[totalReads]           =COALESCE(src.[totalReads]        , trg.[totalReads]        )
  ,[totalLogicalReads]    =COALESCE(src.[totalLogicalReads] , trg.[totalLogicalReads] )
  ,[totalWrites]          =COALESCE(src.[totalWrites]       , trg.[totalWrites]       )
  ,[Writes_in_tempdb]     =COALESCE(src.[Writes_in_tempdb]  , trg.[Writes_in_tempdb]  )
  ,[text]                 =src.[text]
  ,[DBName]               =COALESCE(src.[DBName]            , trg.[DBName]            )
  ,[ObjName]              =COALESCE(src.[ObjName]           , trg.[ObjName]           )
  ,[Wait_type]            =COALESCE(src.[Wait_type]         , trg.[Wait_type]         )
  ,[wait_resource]        =COALESCE(src.[wait_resource]     , trg.[wait_resource]     )
  ,[Login_name]           =COALESCE(src.[Login_name]        , trg.[Login_name]        )
  ,[Host_name]            =COALESCE(src.[Host_name]         , trg.[Host_name]         )
  ,[Blocking_text]        =src.[Blocking_text]
  ,[sql_handle]           =COALESCE(src.[sql_handle]        , trg.[sql_handle]        )
  ,[plan_handle]          =COALESCE(src.[plan_handle]       , trg.[plan_handle]       )
  ,[request_id]           =src.[request_id] 
  ,[user_id]              =src.[user_id] 
  ,[connection_id]        =src.[connection_id]
  ,[transaction_id]       =src.[transaction_id]
  ,[command]              =src.[command]
  ,LevelDetail            =COALESCE(t.LevelDetail, trg.LevelDetail)
  ,IsNotFinishBlockingSession = t.IsNotFinishBlockingSession
  ,IsRootBlocker          =t.IsRootBlocker
  ,RootBlockerSession_ID  =t.RootBlockerSession_ID
 FROM [srv].[RequestStatistics] AS trg
 INNER JOIN @tbl2 AS src ON (trg.[session_id]=src.[session_id])
       AND (trg.[request_id]=src.[request_id])
       AND (trg.[database_id]=src.[database_id])
       AND (trg.[user_id]=src.[user_id])
       AND (trg.[start_time]=src.[start_time])
       AND (trg.[command]=src.[command])
       AND ((trg.[sql_handle]=src.[sql_handle] AND src.[sql_handle] IS NOT NULL) OR (src.[sql_handle] IS NULL))
       AND ((trg.[plan_handle]=src.[plan_handle] AND src.[plan_handle] IS NOT NULL) OR (src.[plan_handle] IS NULL))
       AND ((trg.[transaction_id]=src.[transaction_id] AND src.[transaction_id] IS NOT NULL) OR (src.[transaction_id] IS NULL))
       AND ((trg.[connection_id]=src.[connection_id] AND src.[connection_id] IS NOT NULL) OR (src.[connection_id] IS NULL))
  LEFT JOIN @tbl_ListDetail AS t ON (t.[session_id] = src.[session_id]) AND (t.[start_time] = src.[start_time]);

 UPDATE trg
 SET trg.[EndRegUTCDate] = @DT
 FROM [srv].[RequestStatistics] AS trg
 WHERE NOT EXISTS(
      SELECT TOP(1) 1
      FROM @tbl2 AS src
      WHERE (trg.[session_id]=src.[session_id])
       AND (trg.[request_id]=src.[request_id])
       AND (trg.[database_id]=src.[database_id])
       AND (trg.[user_id]=src.[user_id])
       AND (trg.[start_time]=src.[start_time])
       AND (trg.[command]=src.[command])
       AND ((trg.[sql_handle]=src.[sql_handle] AND src.[sql_handle] IS NOT NULL) OR (src.[sql_handle] IS NULL))
       AND ((trg.[plan_handle]=src.[plan_handle] AND src.[plan_handle] IS NOT NULL) OR (src.[plan_handle] IS NULL))
       AND ((trg.[transaction_id]=src.[transaction_id] AND src.[transaction_id] IS NOT NULL) OR (src.[transaction_id] IS NULL))
       AND ((trg.[connection_id]=src.[connection_id] AND src.[connection_id] IS NOT NULL) OR (src.[connection_id] IS NULL))
      );

 INSERT INTO [srv].[RequestStatistics] ([session_id]
      ,[blocker]
      ,[Status]
      ,[start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[database_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,InsertUTCDate
      ,LevelDetail
      ,IsNotFinishBlockingSession
      ,IsRootBlocker
      ,RootBlockerSession_ID)
 SELECT src.[session_id]
      ,src.[blocker]
      ,src.[Status]
      ,src.[start_time]
      ,src.[percent_complete]
      ,src.[totalElapsedTime]
      ,src.[TotalCPU]
      ,src.[memory_usage_kb]
      ,src.[row_count]
      ,src.[totalReads]
      ,src.[totalLogicalReads]
      ,src.[totalWrites]
      ,src.[Writes_in_tempdb]
      ,src.[text]
      ,src.[DBName]
      ,src.[ObjName]
      ,src.[Wait_type]
      ,src.[wait_resource]
      ,src.[Login_name]
      ,src.[Host_name]
      ,src.[Blocking_text]
      ,src.[sql_handle]
      ,src.[plan_handle]
      ,src.[request_id]
      ,src.[database_id]
      ,src.[user_id]
      ,src.[connection_id]
      ,src.[transaction_id]
      ,src.[command]
      ,@DT
      ,t.LevelDetail
      ,t.IsNotFinishBlockingSession
      ,t.IsRootBlocker
      ,t.RootBlockerSession_ID
 FROM @tbl2 AS src
  LEFT JOIN @tbl_ListDetail AS t ON (t.[session_id] = src.[session_id]) AND (t.[start_time] = src.[start_time])
 WHERE NOT EXISTS(
      SELECT TOP(1) 1
      FROM [srv].[RequestStatistics] AS trg
      WHERE (trg.[session_id]=src.[session_id])
       AND (trg.[request_id]=src.[request_id])
       AND (trg.[database_id]=src.[database_id])
       AND (trg.[user_id]=src.[user_id])
       AND (trg.[start_time]=src.[start_time])
       AND (trg.[command] collate DATABASE_DEFAULT=src.[command] collate DATABASE_DEFAULT)
       AND ((trg.[sql_handle]=src.[sql_handle] AND src.[sql_handle] IS NOT NULL) OR (src.[sql_handle] IS NULL))
       AND ((trg.[plan_handle]=src.[plan_handle] AND src.[plan_handle] IS NOT NULL) OR (src.[plan_handle] IS NULL))
       AND ((trg.[transaction_id]=src.[transaction_id] AND src.[transaction_id] IS NOT NULL) OR (src.[transaction_id] IS NULL))
       AND ((trg.[connection_id]=src.[connection_id] AND src.[connection_id] IS NOT NULL) OR (src.[connection_id] IS NULL))
      );

  DELETE
  FROM srv.RequestStatistics
  WHERE InsertUTCDate <= DateAdd(DAY, -@DayOld, GETUTCDATE());

  IF (
       (DATEPART(HOUR, @DT) BETWEEN 19 AND 23)
       OR
       (DATEPART(HOUR, @DT) BETWEEN 0 AND 3)
     )
  BEGIN
    RETURN;
  END

  INSERT INTO @tbl_blockeds ([session_id], LevelDetail, Login_name, RootBlockerSession_ID, [start_time])
  SELECT t.[session_id], t.LevelDetail, t.Login_name, t.RootBlockerSession_ID, t.[start_time]
  FROM @tbl_ListDetail AS t
  WHERE t.IsRootBlocker = 0
    AND t.IsNotFinishBlockingSession = 0
    AND ((t.Login_name = @LoginName) OR (@LoginName IS NULL))
  
  INSERT INTO @tbl_blockers ([session_id], LevelDetail, start_time, Login_name)
  SELECT td.[session_id], td.LevelDetail, td.start_time, td.Login_name
  FROM @tbl_blockeds AS t
  JOIN @tbl_ListDetail AS td ON (t.RootBlockerSession_ID = td.[session_id])
  GROUP BY td.[session_id], td.LevelDetail, td.start_time, td.Login_name;
  
  UPDATE trg
 SET
  [blocker]              =src.[blocker]
  ,[Status]               =src.[Status]
  ,[percent_complete]     =src.[percent_complete]
  ,[totalElapsedTime]     =COALESCE(src.[totalElapsedTime]  , trg.[totalElapsedTime]  )
  ,[TotalCPU]             =COALESCE(src.[TotalCPU]          , trg.[TotalCPU]          )
  ,[memory_usage_kb]      =COALESCE(src.[memory_usage_kb]   , trg.[memory_usage_kb]   )
  ,[row_count]            =COALESCE(src.[row_count]         , trg.[row_count]         )
  ,[totalReads]           =COALESCE(src.[totalReads]        , trg.[totalReads]        )
  ,[totalLogicalReads]    =COALESCE(src.[totalLogicalReads] , trg.[totalLogicalReads] )
  ,[totalWrites]          =COALESCE(src.[totalWrites]       , trg.[totalWrites]       )
  ,[Writes_in_tempdb]     =COALESCE(src.[Writes_in_tempdb]  , trg.[Writes_in_tempdb]  )
  ,[text]                 =src.[text]
  ,[DBName]               =COALESCE(src.[DBName]            , trg.[DBName]            )
  ,[ObjName]              =COALESCE(src.[ObjName]           , trg.[ObjName]           )
  ,[Wait_type]            =src.[Wait_type]
  ,[wait_resource]        =src.[wait_resource]
  ,[Login_name]           =COALESCE(src.[Login_name]        , trg.[Login_name]        )
  ,[Host_name]            =COALESCE(src.[Host_name]         , trg.[Host_name]         )
  ,[Blocking_text]        =src.[Blocking_text]
  ,[sql_handle]           =COALESCE(src.[sql_handle]        , trg.[sql_handle]        )
  ,[plan_handle]          =COALESCE(src.[plan_handle]       , trg.[plan_handle]       )
  ,[request_id]           =src.[request_id] 
  ,[user_id]              =src.[user_id] 
  ,[connection_id]        =src.[connection_id]
  ,[transaction_id]       =src.[transaction_id]
  ,[command]              =src.[command]
  ,LevelDetail            =b.LevelDetail
  ,RootBlockerSession_ID  =b.RootBlockerSession_ID
 FROM [srv].[RequestBlockedItBlocks] AS trg
 INNER JOIN @tbl2 AS src ON (trg.[session_id]=src.[session_id]) AND (trg.start_time = src.start_time)
  JOIN @tbl_blockeds AS b ON (b.[session_id]=src.[session_id]) AND (b.[start_time] = src.[start_time])
  WHERE src.blocker > 0;

  INSERT INTO [srv].[RequestBlockedItBlocks] ([session_id]
      ,[blocker]
      ,[Status]
      ,[start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[database_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,LevelDetail
      ,InsertUTCDate
      ,RootBlockerSession_ID)
 SELECT src.[session_id]
      ,src.[blocker]
      ,src.[Status]
      ,src.[start_time]
      ,src.[percent_complete]
      ,src.[totalElapsedTime]
      ,src.[TotalCPU]
      ,src.[memory_usage_kb]
      ,src.[row_count]
      ,src.[totalReads]
      ,src.[totalLogicalReads]
      ,src.[totalWrites]
      ,src.[Writes_in_tempdb]
      ,src.[text]
      ,src.[DBName]
      ,src.[ObjName]
      ,src.[Wait_type]
      ,src.[wait_resource]
      ,src.[Login_name]
      ,src.[Host_name]
      ,src.[Blocking_text]
      ,src.[sql_handle]
      ,src.[plan_handle]
      ,src.[request_id]
      ,src.[database_id]
      ,src.[user_id]
      ,src.[connection_id]
      ,src.[transaction_id]
      ,src.[command]
      ,b.LevelDetail
      ,@DT
      ,b.RootBlockerSession_ID
 FROM @tbl2 AS src
  JOIN @tbl_blockeds AS b ON (b.[session_id]=src.[session_id]) AND (b.[start_time]=src.[start_time])
  WHERE src.blocker > 0
    AND NOT EXISTS(
                    SELECT 1
                    FROM srv.[RequestBlockedItBlocks] AS RBIB
                    WHERE RBIB.[session_id] = src.[session_id]
                      AND RBIB.start_time = src.start_time
                  );

  DELETE FROM trg
  FROM srv.[RequestBlockedItBlocks] AS trg
  WHERE NOT EXISTS (
                    SELECT 1
                    FROM @tbl_blockeds AS src
                    WHERE src.[session_id] = trg.[session_id]
                      AND src.start_time   = trg.start_time
                   );

  INSERT INTO [srv].[RequestBlocked] ([session_id]
      ,[blocker]
      ,[Status]
      ,[start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[database_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,[LevelDetail]
      ,InsertUTCDate)
 SELECT src.[session_id]
      ,src.[blocker]
      ,src.[Status]
      ,src.[start_time]
      ,src.[percent_complete]
      ,src.[totalElapsedTime]
      ,src.[TotalCPU]
      ,src.[memory_usage_kb]
      ,src.[row_count]
      ,src.[totalReads]
      ,src.[totalLogicalReads]
      ,src.[totalWrites]
      ,src.[Writes_in_tempdb]
      ,src.[text]
      ,src.[DBName]
      ,src.[ObjName]
      ,src.[Wait_type]
      ,src.[wait_resource]
      ,src.[Login_name]
      ,src.[Host_name]
      ,src.[Blocking_text]
      ,src.[sql_handle]
      ,src.[plan_handle]
      ,src.[request_id]
      ,src.[database_id]
      ,src.[user_id]
      ,src.[connection_id]
      ,src.[transaction_id]
      ,src.[command]
      ,bls.[LevelDetail]
      ,@DT
 FROM @tbl2 AS src
  JOIN @tbl_blockers AS bls ON (bls.[session_id] = src.[session_id]) AND (bls.start_time = src.start_time)
  WHERE
    NOT EXISTS (
                    SELECT 1
                    FROM [srv].[RequestBlocked] AS bb
                    WHERE bb.[session_id] = src.[session_id]
                      AND bb.start_time   = src.start_time
                   )
    AND (src.blocker <= 0)
    AND (
          (
            (src.[TSQL] NOT IN ('xp_cmdshell'))
            AND (
                  src.[command] NOT IN (
                                        'RESTORE DATABASE',
                                        'RECOVERY',
                                        'BACKUP DATABASE',
                                        'BACKUP LOG',
                                        'ROLLBACK',
                                        'ALTER INDEX'
                                       )
                )
          )
          OR (src.[TSQL] IS NULL)
        );

  DELETE FROM trg
  FROM srv.[RequestBlocked] AS trg
  LEFT JOIN srv.SQLQuery AS SQ ON SQ.[SQLHandle] = trg.[sql_handle]
  WHERE
    NOT EXISTS (
                SELECT 1
                FROM @tbl_blockers AS bls
                WHERE bls.[session_id] = trg.[session_id]
                  AND bls.start_time   = trg.start_time
               )
               OR (trg.blocker > 0)
               OR (SQ.[TSQL] IN ('xp_cmdshell'))
               OR (
                     trg.[command] IN (
                                           'RESTORE DATABASE',
                                           'RECOVERY',
                                           'BACKUP DATABASE',
                                           'BACKUP LOG',
                                           'ROLLBACK',
                                           'ALTER INDEX'
                                          )
                  );

  UPDATE trg
 SET
  [blocker]              =src.[blocker]
  ,[Status]               =src.[Status]
  ,[percent_complete]     =src.[percent_complete]
  ,[totalElapsedTime]     =COALESCE(src.[totalElapsedTime]  , trg.[totalElapsedTime]  )
  ,[TotalCPU]             =COALESCE(src.[TotalCPU]          , trg.[TotalCPU]          )
  ,[memory_usage_kb]      =COALESCE(src.[memory_usage_kb]   , trg.[memory_usage_kb]   )
  ,[row_count]            =COALESCE(src.[row_count]         , trg.[row_count]         )
  ,[totalReads]           =COALESCE(src.[totalReads]        , trg.[totalReads]        )
  ,[totalLogicalReads]    =COALESCE(src.[totalLogicalReads] , trg.[totalLogicalReads] )
  ,[totalWrites]          =COALESCE(src.[totalWrites]       , trg.[totalWrites]       )
  ,[Writes_in_tempdb]     =COALESCE(src.[Writes_in_tempdb]  , trg.[Writes_in_tempdb]  )
  ,[text]                 =src.[text]
  ,[DBName]               =COALESCE(src.[DBName]            , trg.[DBName]            )
  ,[ObjName]              =COALESCE(src.[ObjName]           , trg.[ObjName]           )
  ,[Wait_type]            =src.[Wait_type]
  ,[wait_resource]        =src.[wait_resource]
  ,[Login_name]           =COALESCE(src.[Login_name]        , trg.[Login_name]        )
  ,[Host_name]            =COALESCE(src.[Host_name]         , trg.[Host_name]         )
  ,[Blocking_text]        =src.[Blocking_text]
  ,[sql_handle]           =COALESCE(src.[sql_handle]        , trg.[sql_handle]        )
  ,[plan_handle]          =COALESCE(src.[plan_handle]       , trg.[plan_handle]       )
  ,[request_id]           =src.[request_id] 
  ,[user_id]              =src.[user_id] 
  ,[connection_id]        =src.[connection_id]
  ,[transaction_id]       =src.[transaction_id]
  ,[command]              =src.[command]
  ,[Count]                += 1
  ,LevelDetail            = bls.LevelDetail
 FROM [srv].[RequestBlocked] AS trg
 INNER JOIN @tbl2 AS src ON (trg.[session_id]=src.[session_id]) AND (trg.start_time = src.start_time)
  JOIN @tbl_blockers AS bls ON (bls.[session_id] = src.[session_id]) AND (trg.start_time = src.start_time);

  DECLARE @TSQL NVARCHAR(2000) = '';

  SELECT @TSQL = CONCAT(@TSQL, 'KILL ', trg.[session_id], ';')
    FROM [srv].[RequestBlocked] AS trg
    WHERE trg.[Count] >= @Count
      AND trg.blocker <= 0;

  BEGIN TRAN
    EXEC sys.sp_executesql @stmt = @TSQL;

    INSERT INTO [srv].[RequestBlockedKilled]
             ([session_id]
             ,[blocker]
             ,[Status]
             ,[start_time]
             ,[percent_complete]
             ,[totalElapsedTime]
             ,[TotalCPU]
             ,[memory_usage_kb]
             ,[row_count]
             ,[totalReads]
             ,[totalLogicalReads]
             ,[totalWrites]
             ,[Writes_in_tempdb]
             ,[text]
             ,[DBName]
             ,[ObjName]
             ,[Wait_type]
             ,[wait_resource]
             ,[Login_name]
             ,[Host_name]
             ,[Blocking_text]
             ,[sql_handle]
             ,[plan_handle]
             ,[request_id]
             ,[database_id]
             ,[user_id]
             ,[connection_id]
             ,[transaction_id]
             ,[command]
             ,LevelDetail
             ,InsertUTCDate)
    SELECT src.[session_id]
             ,src.[blocker]
             ,src.[Status]
             ,src.[start_time]
             ,src.[percent_complete]
             ,src.[totalElapsedTime]
             ,src.[TotalCPU]
             ,src.[memory_usage_kb]
             ,src.[row_count]
             ,src.[totalReads]
             ,src.[totalLogicalReads]
             ,src.[totalWrites]
             ,src.[Writes_in_tempdb]
             ,src.[text]
             ,src.[DBName]
             ,src.[ObjName]
             ,src.[Wait_type]
             ,src.[wait_resource]
             ,src.[Login_name]
             ,src.[Host_name]
             ,src.[Blocking_text]
             ,src.[sql_handle]
             ,src.[plan_handle]
             ,src.[request_id]
             ,src.[database_id]
             ,src.[user_id]
             ,src.[connection_id]
             ,src.[transaction_id]
             ,src.[command]
             ,src.LevelDetail
             ,src.InsertUTCDate
    FROM [srv].[RequestBlocked] AS src
    WHERE src.[Count] >= @Count
      AND src.blocker <= 0;

    INSERT INTO [srv].[RequestBlockedItBlocksArchive]
           ([session_id]
           ,[blocker]
           ,[Status]
           ,[start_time]
           ,[percent_complete]
           ,[totalElapsedTime]
           ,[TotalCPU]
           ,[memory_usage_kb]
           ,[row_count]
           ,[totalReads]
           ,[totalLogicalReads]
           ,[totalWrites]
           ,[Writes_in_tempdb]
           ,[text]
           ,[DBName]
           ,[ObjName]
           ,[Wait_type]
           ,[wait_resource]
           ,[Login_name]
           ,[Host_name]
           ,[Blocking_text]
           ,[sql_handle]
           ,[plan_handle]
           ,[request_id]
           ,[database_id]
           ,[user_id]
           ,[connection_id]
           ,[transaction_id]
           ,[command]
           ,InsertUTCDate
           ,LevelDetail
           ,RootBlockerSession_ID)
    SELECT src.[session_id]
           ,src.[blocker]
           ,src.[Status]
           ,src.[start_time]
           ,src.[percent_complete]
           ,src.[totalElapsedTime]
           ,src.[TotalCPU]
           ,src.[memory_usage_kb]
           ,src.[row_count]
           ,src.[totalReads]
           ,src.[totalLogicalReads]
           ,src.[totalWrites]
           ,src.[Writes_in_tempdb]
           ,src.[text]
           ,src.[DBName]
           ,src.[ObjName]
           ,src.[Wait_type]
           ,src.[wait_resource]
           ,src.[Login_name]
           ,src.[Host_name]
           ,src.[Blocking_text]
           ,src.[sql_handle]
           ,src.[plan_handle]
           ,src.[request_id]
           ,src.[database_id]
           ,src.[user_id]
           ,src.[connection_id]
           ,src.[transaction_id]
           ,src.[command]
           ,src.InsertUTCDate
           ,src.LevelDetail
           ,src.RootBlockerSession_ID
    FROM [srv].[RequestBlockedItBlocks] AS src
    WHERE EXISTS (
                  SELECT 1
                  FROM [srv].[RequestBlocked] AS RB
                  WHERE RB.[session_id] = src.blocker
                    AND RB.[Count] >= @Count
                    AND RB.blocker <= 0
                 );

    DELETE FROM RBIB
    FROM [srv].[RequestBlockedItBlocks] AS RBIB
    WHERE EXISTS (
                  SELECT 1
                  FROM [srv].[RequestBlocked] AS RB
                  WHERE RB.[session_id] = RBIB.blocker
                    AND RB.[Count] >= @Count
                    AND RB.blocker <= 0
                 );
     
    DELETE FROM RB
    FROM [srv].[RequestBlocked] AS RB
    WHERE RB.[Count] >= @Count
      AND RB.blocker <= 0;
  COMMIT TRAN
END

DECLARE @DT DATETIME = '2026-02-27T12:35:00';
 
--кто был удалён автокиллером
SELECT *
FROM srv.vRequestBlockedKilled WITH(NOLOCK)
WHERE InsertUTCDate >= @DT
ORDER BY InsertUTCDate desc;
 
--кто был заблокирован
SELECT *
FROM srv.vRequestBlockedItBlocksArchive WITH(NOLOCK)
WHERE InsertUTCDate >= @DT
ORDER BY InsertUTCDate desc;

--кто заблокирован по данным последнего снимка активных сеансов
SELECT *
FROM srv.vRequestBlockedItBlocks WITH(NOLOCK)
ORDER BY InsertUTCDate desc;
 
--кто блокирует по данным последнего снимка активных сеансов
SELECT *
ROM srv.vRequestBlocked WITH(NOLOCK)
ORDER BY InsertUTCDate desc;

SELECT [session_id]
      ,[blocker]
      ,[Status]
      ,[Start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[TSQL]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[QueryPlan]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,[database_id]
  FROM [inf].[vRequestDetail] WITH(NOLOCK)
  ORDER BY Start_time ASC;

--рассматриваем запись из представления srv.vRequestBlockedItBlocksArchive:
--InsertUTCDate = '2026-03-05T12:14:30.987' и blocked = 245
 
DECLARE @InsertUTCDate DATETIME = '2026-03-11T09:06:50.977'; --это значение взяли из поля InsertUTCDate из представления srv.vRequestBlockedItBlocksArchive
DECLARE @blocker        INT     = 245--это значение взяли из поля blocker из представления srv.vRequestBlockedItBlocksArchive
DECLARE @start_time DATETIME;
 
SELECT @start_time = RBK.start_time
FROM srv.RequestBlockedKilled AS RBK WITH(NOLOCK)
WHERE RBK.InsertUTCDate = @InsertUTCDate
  AND RBK.[session_id]  = @blocker;
 
SELECT [session_id]
      ,[blocker]
      ,[Status]
      ,[start_time]
      ,[percent_complete]
      ,[totalElapsedTime]
      ,[TotalCPU]
      ,[memory_usage_kb]
      ,[row_count]
      ,[totalReads]
      ,[totalLogicalReads]
      ,[totalWrites]
      ,[Writes_in_tempdb]
      ,[text]
      ,[DBName]
      ,[ObjName]
      ,[Wait_type]
      ,[wait_resource]
      ,[Login_name]
      ,[Host_name]
      ,[Blocking_text]
      ,[QueryPlan]
      ,[sql_handle]
      ,[plan_handle]
      ,[request_id]
      ,[database_id]
      ,[user_id]
      ,[connection_id]
      ,[transaction_id]
      ,[command]
      ,[InsertUTCDate]
      ,[EndRegUTCDate]
      ,[TSQL]
      ,[RootBlockerSession_ID]
      ,[IsNotFinishBlockingSession]
      ,[LevelDetail]
  FROM [srv].[vRequestStatistics] AS RS WITH(NOLOCK)
  WHERE (RS.InsertUTCDate = @InsertUTCDate)
       OR (
            (RS.[session_id] = @blocker)
        AND (RS.start_time = @start_time)
        AND (RS.LevelDetail IS NOT NULL)
          )
  ORDER BY RS.start_time ASC;