SQL Server 2016+如何查询表过往持有的锁？排查生产超时问题

问题背景 #

• 生产环境出现事务锁引发的超时问题，但无法复现该场景
• 需要定位导致阻塞的事务，优先获取对应执行语句以识别关联进程
• 已尝试使用who_sp2及以下动态管理视图查询，但这类方法仅在阻塞发生时有效，无法回溯过往阻塞记录：

  FROM sys.dm_tran_active_transactions tActive
  JOIN sys.dm_tran_session_transactions tSession
      ON (tSession.transaction_id = tActive.transaction_id) LEFT OUTER
  JOIN sys.dm_exec_sessions AS tExec
      ON tSession.session_id = tExec.session_id LEFT OUTER
  JOIN sys.dm_exec_requests AS tRequest
  

解决方案：追踪过往阻塞记录的三种方式 #

1. 启用扩展事件（Extended Events）长期追踪 #

扩展事件是轻量级低开销的追踪工具，适合生产环境长期监控：

• 创建阻塞追踪会话：

  CREATE EVENT SESSION [Blocked_Process_Tracking] ON SERVER 
  ADD EVENT sqlserver.blocked_process_report(
      ACTION(sqlserver.sql_text,sqlserver.session_id,sqlserver.username)),
  ADD EVENT sqlserver.process_blocked(
      ACTION(sqlserver.sql_text,sqlserver.session_id,sqlserver.username))
  ADD TARGET package0.event_file(SET filename=N'Blocked_Process_Tracking.xel',max_file_size=(100),max_rollover_files=(5))
  WITH (STARTUP_STATE=ON); -- 服务器重启后自动启动会话
  

• 启动会话：

  ALTER EVENT SESSION [Blocked_Process_Tracking] ON SERVER STATE = START;
  

• 事后分析追踪文件：

  SELECT 
      event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name,
      event_data.value('(event/@timestamp)[1]', 'datetime2(7)') AS event_time,
      event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/spid)[1]', 'int') AS blocked_spid,
      event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/spid)[1]', 'int') AS blocking_spid,
      event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS blocked_sql_text,
      event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/inputbuf)[1]', 'nvarchar(max)') AS blocking_sql_text
  FROM 
      (SELECT CAST(event_data AS XML) AS event_data
       FROM sys.fn_xe_file_target_read_file('Blocked_Process_Tracking*.xel', NULL, NULL, NULL)) AS x;
  

2. 配置阻塞进程阈值+SQL Server Agent告警记录 #

通过设置阈值，触发时自动记录阻塞信息到日志表：

• 设置阻塞进程阈值（单位：秒，示例设为5秒）：

  sp_configure 'show advanced options', 1;
  RECONFIGURE;
  sp_configure 'blocked process threshold', 5;
  RECONFIGURE;
  

• 创建阻塞日志表：

  CREATE TABLE BlockedProcessLog (
      LogID INT IDENTITY(1,1) PRIMARY KEY,
      EventTime DATETIME DEFAULT GETDATE(),
      BlockedSPID INT,
      BlockingSPID INT,
      BlockedSQL NVARCHAR(MAX),
      BlockingSQL NVARCHAR(MAX),
      BlockedLogin NVARCHAR(128),
      BlockingLogin NVARCHAR(128)
  );
  

• 创建SQL Server Agent作业，执行以下语句写入日志：

  INSERT INTO BlockedProcessLog (BlockedSPID, BlockingSPID, BlockedSQL, BlockingSQL, BlockedLogin, BlockingLogin)
  SELECT 
      blocked.session_id AS BlockedSPID,
      blocking.session_id AS BlockingSPID,
      blocked_sql.text AS BlockedSQL,
      blocking_sql.text AS BlockingSQL,
      blocked.login_name AS BlockedLogin,
      blocking.login_name AS BlockingLogin
  FROM 
      sys.dm_exec_requests blocked
  JOIN 
      sys.dm_exec_requests blocking ON blocked.blocking_session_id = blocking.session_id
  CROSS APPLY 
      sys.dm_exec_sql_text(blocked.sql_handle) blocked_sql
  CROSS APPLY 
      sys.dm_exec_sql_text(blocking.sql_handle) blocking_sql;
  

• 创建WMI事件警报，触发条件为SELECT * FROM BLOCKED_PROCESS_REPORT，关联上述作业实现自动记录。

3. 查询系统默认健康会话记录 #

SQL Server默认启用的系统健康会话会自动记录部分阻塞事件，可直接查询：

SELECT 
    event_data.value('(event/@timestamp)[1]', 'datetime2(7)') AS event_time,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/spid)[1]', 'int') AS blocked_spid,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/spid)[1]', 'int') AS blocking_spid,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocked-process/inputbuf)[1]', 'nvarchar(max)') AS blocked_sql,
    event_data.value('(event/data[@name="blocked_process"]/value/blocked-process-report/blocking-process/inputbuf)[1]', 'nvarchar(max)') AS blocking_sql
FROM 
    (SELECT CAST(event_data AS XML) AS event_data
     FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)) AS x
WHERE 
    event_data.value('(event/@name)[1]', 'varchar(50)') = 'blocked_process_report';

内容的提问来源于stack exchange，提问作者Lotar9