Реализация автоудаления блокирующих сессий в MS SQL / forpes.ru

Главная
Реализация автоудаления блокирующих сессий в MS SQL

Реализация автоудаления блокирующих сессий в MS SQL +29

24.04.2026 11:40

jobgemws 0 2900 Источник

Привет, Хабр! Меня зовут Евгений Грибков, я ведущий разработчик в центре технологий VK. В этой статье я покажу решение, к которому мы с коллегами пришли при работе над одной из наших внутренних систем.

Достаточно часто бывает так, что нужно сохранить стабильность работы системы вне зависимости от того, какие запросы выполняются в СУБД. Достичь этого можно разными способами. Я покажу одно из специфических решений — реализацию автокиллера: удаление сессий, которые достаточно долго блокируют запросы других сессий, исходящие от пользователей информационной системы.

В основе решения лежит идея автоматизации удаления забытых транзакций.

На входе имеем:

Под определённым логином из серверного приложения в СУБД выполняются все запросы от пользователей (для простоты будем считать, что критерием этого различия является один логин).
Все запросы (в том числе интеграционные) реализованы таким образом, что их прерывание корректно откатывает внесённые изменения.

Замечание. Если хотя бы один из пунктов не выполнен, то описанная ниже реализация не подойдёт. При невыполнении второго пункта информационная система окажется весьма уязвима к обрыванию какого-либо процесса. А если не выполняется только первый пункт, то важно как-то различать запросы от пользователей и фоновые запросы, и учитывать это различие в реализации автокиллера.

Создание таблиц и представлений

Как всегда, сначала опишу реализацию необходимых таблиц для хранения собранной информации.

Создадим схемы srv и inf:

CREATE SCHEMA [srv];
GO
CREATE SCHEMA [inf];
GO

Создадим таблицу srv.SQLQuery для хранения самих запросов.

Определение таблицы srv.SQLQuery

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

Создадим таблицу srv.PlanQuery для хранения планов запросов.

Определение таблицы srv.PlanQuery

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

Создадим таблицу srv.RequestStatistics для хранения снимков активных запросов сессий.

Определение талицы srv.RequestStatistics

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

Создадим таблицу srv.RequestBlocked для хранения кандидатов сессий на удаление.

Определение таблицы srv.RequestBlocked

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

Создадим таблицу srv.RequestBlockedItBlocks для хранения тех, кто в данный момент блокируется.

Определение таблицы srv.RequestBlockedItBlocks

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

Создадим таблицу srv.RequestBlockedKilled для хранения тех сессий, которые были убиты автокиллером.

Определение таблицы srv.RequestBlockedKilled

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

Создадим таблицу srv.RequestBlockedItBlocksArchive для хранения заблокированных сессий, при этом автокиллер убивает тех, кто блокировал эти сессии.

Определение таблицы srv.RequestBlockedItBlocksArchive

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

Теперь создадим представления для созданных выше таблиц.

Сначала нужно определить представление inf.vRequestDetail для получения снимка активных запросов сессий.

Определение представления inf.vRequestDetail

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;

Здесь используются следующие системные объекты:

Создадим представление srv.vRequestStatistics для таблицы srv.RequestStatistics

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];

Создадим представление srv.vRequestBlocked для таблицы srv.RequestBlocked

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];

Создадим представление srv.vRequestBlockedItBlocks для таблицы srv.RequestBlockedItBlocks

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];

Создадим представление srv.vRequestBlockedItBlocksArchive для таблицы srv.RequestBlockedItBlocksArchive

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];

Создадим представление srv.vRequestBlockedKilled для таблицы srv.RequestBlockedKilled

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];

Описание полей таблиц и представлений

Стандартные поля описаны в sys.dm _exec_sessions и sys.dm _exec_requests.

blocker — это идентификатор сессии, которая блокирует текущую сессию session_id. Он равен 0, если блокирующей сессии нет
Blocking_text — какой фрагмент кода блокируется
TSQL — полный скрипт запроса
QueryPlan — план запроса
text — какой фрагмент кода выполняется в текущий момент
InsertUTCDate — момент сохранения записи о сеансе в UTC (не меняется при копировании в другие таблицы)
IsNotFinishBlockingSession — в цепочке блокираторов есть тот, кого нет в рассматриваемом снимке сеансов по InsertUTCDate (считаем, что это корневой сеанс, но точно мы не знаем; возможно, кто-то другой тоже его блокировал)
IsRootBlocker — является ли сеанс корневым блокиратором, то есть сеансом, который блокирует кого-то, но у самого bloker = 0 в рассматриваемом снимке сеансов по InsertUTCDate
RootBlockerSession_ID — идентификатор корневого сеанса (session_id), который блокирует текущий рассматриваемый сеанс в рассматриваемом снимке сеансов по InsertUTCDate
LevelDetail — детальная информация о цепочках блокировок

Вычисляется поле LevelDetail следующим образом:

если blocker > 0, то формат записи будет следующим: [{"Level":<N+1>,"LevelDetail":[session_id_1,session_id_2, ..., session_id_N]}], где N — это количество сессий в цепочке. При этом сессия session_id_1 блокирует сессию session_id_2 и так далее, то есть session_id_<N-1> блокирует сессию session_id_N, где session_id_N = session_id текущей записи. Иными словами, блокировка идёт слева направо: session_id_1 → session_id_2 → ... → session_id_N
если blocker = 0, то формат записи будет содержать все цепочки блокировок следующего формата: [{"Level":<N+1>,"LevelDetail":[session_id_1,session_id_2, ..., session_id_N]},...], где N — это количество сессий в цепочке, при этом сессия session_id_1 блокируется сессией session_id_2 и так далее, то есть session_id_<N-1> блокируется сессией session_id_N, где session_id_N = session_id текущей записи. Иными словами, блокировка идёт справа налево: session_id_1 ← session_id_2 ← ... ← session_id_N

Таких цепочек может быть много, например:

Пример значения поля LevelDetail

[
  {
    "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
    ]
  }
]

Здесь сессия с идентификатором 245 блокирует семь других сеансов по следующим цепочкам:

195 ← 91 ← 245;
194 ← 245;
187 ← 245;
113 ← 245;
112 ← 195 ←91 ← 245;
99 ← 245;
91 ← 245.

Пример, как распарсить значение из поля LevelDetail:

Пример

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

Итак, мы создали следующие таблицы:

srv.RequestStatistics — таблица снимков активных запросов на каждый момент времени по полю InsertUTCDate;
srv.SQLQuery — таблица запросов: SQLHandle = sql_handle, где TSQL — текст запроса;
srv.PlanQuery — таблица планов: PlanHandle = plan_handle, где PlanQuery — XML-план запроса;
srv.RequestBlocked — таблица сеансов, блокирующих другие сеансы по заданному правилу;
srv.RequestBlockedItBlocks — таблица блокируемых сеансов;
srv.RequestBlockedItBlocksArchive — таблица тех сеансов, которые были блокированы, и эти блокираторы в лице других сеансов были удалены автокиллером по заданному правилу;
srv.RequestBlockedKilled — таблица сеансов, которые были удалены автокиллером по заданному правилу.

Также мы создали следующие представления к этим таблицам:

inf.vRequestDetai — текущие активные сессии;
srv.vRequestBlocked — представление для таблицы srv.RequestBlocked;
srv.vRequestBlockedItBlocks — представление для таблицы srv.RequestBlockedItBlocks;
srv.vRequestBlockedItBlocksArchive — представление для таблицы srv.RequestBlockedItBlocksArchive`;
srv.vRequestBlockedKilled — представление для таблицы srv.RequestBlockedKilled.

Реализация автокиллера

Осталось привести реализацию автокиллера в хранимой процедуры srv.AutoStatisticsActiveRequests, в которой:

таблицы будут заполняться данными;
будет определяться правило срабатывания удаления сеанса;
будут удаляться корневые блокирующие сеансы.

Это правило будет формулироваться следующим образом: если сеанс не блокируется никем, и при этом сам блокирует N раз хотя бы один другой сеанс, запущенный под заданным логином или без него, то такой сеанс удаляется автокиллером. N задаётся через входной параметр @Count (по умолчанию равен 5). По умолчанию @LoginName = <login>, но если передать NULL, то фильтр по логину не будет применяться, то есть будут рассматриваться все сеансы вне зависимости от того, под какими логинами они запущены.

Хранимая процедура будет запускаться через задачу агента MS SQL «Сбор статистики по запросам и автоудаление долгих блокирующих сессий» каждые 10 сек.

Также хранимая процедура будет чистить таблицу srv.RequestStatistics по входному параметру @DayOld (сколько дней хранить данные).

Реализация хранимой процедуры srv.AutoStatisticsActiveRequests

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

Опишу алгоритм работы метода srv.AutoStatisticsActiveRequests (автокиллера):

Читаем снимок активных сеансов из представления inf.vRequestDetail, а результат сохраняем в табличную переменную @tbl2.
По записям из @tbl2 выбираем уникальные запросы и уникальные планы выполнения, результаты сохраняем в табличные переменные @tbl0 и @tbl1 соответственно.
Объединяем таблицы @tbl0 и srv.SQLQuery по sql_handle, если удаётся определить текст запроса, и таблицы @tbl1 и srv.PlanQuery по кортежу (sql_handle, plan_handle), если удаётся определить план запроса
В табличную переменную @tbl3 собираем следующую информацию из табличной переменной @tbl2:
1. session_id — идентификатор сессии;
2. blocker — идентификатор сессии, которая блокирует текущую сессию session_id, или 0, если блокирующей сессии нет;
3. Login_name — логин, под которым работает сессия;
4. IsNotFinishBlockingSession — является ли сессия добавочной, то есть её нет в рассматриваемом снимке сеансов и мы её добавили из blocker (при этом blocker > 0) других сессий в этом же снимке (здесь всегда 0);
5. start_time — момент начала работы запроса.
  Эту табличную переменную @tbl3 мы дополняем недостающими блокирующими сеансами по blocker (при этом blocker > 0) других сессий в этом же снимке, где:
  1. session_id — blocker из других сессий в этом же снимке;
  2. blocker = 0;
  3. Login_name = NULL;
  4. IsNotFinishBlockingSession = 1;
  5. start_time — текущий момент времени
В табличную переменную @tbl_ListDetail кладём все значения для LevelDetail, обходя граф сессий из табличной переменной @tbl3. Эта табличная переменная содержит те же поля, что и табличная переменная @tbl3, а также в ней есть следующие поля:
1. IsRootBlocker — является ли сеанс корневым блокиратором, то есть блокирующим сеансом, у которого bloker = 0;
2. RootBlockerSession_ID — идентификатор корневого сеанса (session_id), который блокирует текущий рассматриваемый сеанс в рассматриваемом снимке сеансов.
Добавляем и обновляем данные в таблице srv.RequestStatistics по кортежу (session_id, request_id, database_id, user_id, start_time, command), а также если значение задано по кортежу (sql_handle, plan_handle, transaction_id, connection_id) из табличных переменных @tbl3 и @tbl_ListDetail (из @tbl_ListDetail по кортежу (session_id, start_time). Если запись есть в таблице srv.RequestStatistics, но её нет в табличной переменной @tbl3 по заданному кортежу, то в поле EndRegUTCDate таблицы srv.RequestStatistics проставляется момент времени взятия снимка активных сеансов в формате UTC
Очищаем таблицу srv.RequestStatistics по InsertUTCDate — данные старее, чем @DayOld дней относительно текущего момента в формате UTC.
Наполняем табличную переменную @tbl_blockeds из табличной переменной @tbl_ListDetail, у которых IsRootBlocker = 0 и IsNotFinishBlockingSession = 0, а также Login_name = @LoginName. Если последний не NULL, то есть задан, иначе без этого фильтра — здесь собираются все сеансы, которых блокируют.
Наполняем табличную переменную @tbl_blockers из табличной переменной @tbl_blockeds по уникальным кортежам (session_id, LevelDetail, start_time, Login_name), то есть здесь собираются все блокирующие сеансы.
Вставляем, обновляем и удаляем данные в таблице dbo.RequestBlockedItBlocks по табличным переменным @tbl2 и @tbl_blockeds по кортежу (session_id, start_time), и у кого в @tbl2: blocker > 0. Удаление происходит, если нет для записи в таблице dbo.RequestBlockedItBlocks соответствующей записи в табличной переменной @tbl_blockeds по кортежу (session_id, start_time).
Аналогично предыдущему пункту вставляем, обновляем и удаляем данные в таблице srv.RequestBlocked по табличным переменным @tbl2 и @tbl_blockeds по кортежу (session_id, start_time) и у кого в @tbl2: blocker > 0. При вставке в таблицу srv.RequestBlocked в поле Count = 1, а при обновлении Count увеличиваем на 1. Удаляем, если нет для записи в таблице dbo.RequestBlockedItBlocks соответствующей записи в табличной переменной @tbl_blockers по кортежу (session_id, start_time) и у кого в @tbl2: blocker <= 0. При этом в таблицу srv.RequestBlocked не попадают следующие записи (или сразу удаляются, если выявлено нарушение хотя бы одного из следующих правил):
1. запрос xp_cmdshell
2. команды:
  1. RESTORE DATABASE
  2. RECOVERY
  3. BACKUP DATABASE
  4. BACKUP LOG
  5. ROLLBACK
  6. ALTER INDEX
Собираем все идентификаторы сессий из таблицы srv.RequestBlocked, у которых Count >= @Count, и формируем динамический скрипт их удаления через команду KILL. Формат: KILL <session_id_1>; KILL <session_id_2>; ...; KILL <sesion_id_M>.
В одной явной транзакции удаляем выявленные сессии с последующим переносом данных по следующей схеме:
1. srv.RequestBlocked → srv.RequestBlockedKilled;
2. srv.RequestBlockedItBlocks → srv.RequestBlockedItBlocksArchive.

Типовые запросы для анализа

Приведём типовые запросы по анализу данных работы автокиллером.

Кто был удалён автокиллером с определённого момента времени можно узнать с помощью следующего запроса:

Кто был удалён автокиллером

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;

Кто находится на рассмотрении на удаление автокиллером (то есть может быть удалён, если счётчик Count станет @Count):

Кто находится на рассмотрении на удаление автокиллером

--кто заблокирован по данным последнего снимка активных сеансов
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;

На этом всё. Мы рассмотрели реализацию автокиллера — процесса, который определяет корневые блокирующие сессии (блокирующие пользовательские сессии) с последующим их удалением, если эта блокировка держится достаточно долго.

Этот процесс позволяет избежать снижения производительности всей СУБД из-за долго блокирующих запросов.

Реализация автоудаления блокирующих сессий в MS SQL +29

Создание таблиц и представлений

Описание полей таблиц и представлений

Реализация автокиллера

Типовые запросы для анализа

Источники:

Комментарии (0)