如何在SQL中通过动态视图监控数据库实时会话与死锁-SQL-PHP中文网

sys.dm_exec_sessions 和 sys.dm_exec_requests 无法监控死锁，因其仅提供瞬时快照且不保留死锁上下文；真正可靠的是扩展事件（如 system_health 中的 xml_deadlock_report）。

如何在sql中通过动态视图监控数据库实时会话与死锁

sys.dm_exec_sessions 和 sys.dm_exec_requests 这两个动态管理视图（DMV）本身不能“监控死锁”，它们只反映某一毫秒的会话快照。死锁一旦被 SQL Server 检测并解除，相关会话通常已回滚或终止，sys.dm_exec_requests 中的记录迅速消失，sys.dm_exec_sessions 也不保留死锁上下文。你查不到死锁链，不是查询写得不对，而是数据根本没在那里。

真正能捕获死锁的唯一可靠来源是扩展事件（XEvent），尤其是预启用的 system_health 会话。它默认收集 xml_deadlock_report 事件，保留在环形缓冲区中（约 4 小时，受内存压力影响）。别试图用视图“实时”看死锁——那是误解了死锁的本质：它是瞬态冲突事件，不是持续状态。

要查最近发生的死锁图，执行：

SELECT CAST(target_data AS XML) 
FROM sys.dm_xe_session_targets st 
JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address 
WHERE s.name = 'system_health'

然后在返回的 XML 中搜索节点。注意：结果是 XML，需展开查看资源争用、进程堆栈、输入缓冲区等完整现场。

怎么用 DMV 查当前阻塞链（不是死锁）

阻塞是可观察的瞬时状态，但比死锁“活得久一点”。关键不是单次查询，而是理解 blocking_session_id 的含义和局限：

blocking_session_id > 0 表示该请求正被另一个会话阻塞
但这个值只在阻塞发生当下有效；一旦被 Kill 或超时释放，就归零
单靠 sys.dm_exec_requests 不知道谁持锁，必须关联 sys.dm_tran_locks 和 sys.dm_exec_sql_text

推荐组合查询（带锁表名和 SQL 文本）：

DrugBank 数据库

访问并分析来自 DrugBank 数据库的全面药物信息，包括药物属性、相互作用、靶点、通路、化学结构和药理学数据。用于药物数据、药物发现研究、药理学研究、药物-

下载

SELECT 
  r.session_id,
  r.blocking_session_id,
  t.text AS [SQL语句],
  o.name AS [被锁表名],
  l.resource_type,
  l.request_mode,
  l.request_status
FROM sys.dm_tran_locks l
JOIN sys.partitions p ON l.resource_associated_entity_id = p.hobt_id
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.dm_exec_requests r ON l.request_session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0 OR l.request_session_id IN (
  SELECT blocking_session_id 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id > 0
)

sys.dm_tran_locks 是锁信息唯一权威源，resource_description 可定位到具体页或键
sys.dm_exec_sql_text(r.sql_handle) 必须用 CROSS APPLY，不能用 LEFT JOIN（否则可能丢行）
如果 t.text 为空，说明该会话已结束或 handle 失效，需结合 sys.dm_exec_sessions 查 login_time 和 last_request_end_time 判断活跃性

为什么不能用 `sp_who2` 或 `sp_lock` 查死锁

sp_who2 和 sp_lock 在 SQL Server 2022 中已被标记为“过时”，微软明确不推荐用于生产诊断：

sp_who2 仅包装 sys.dm_exec_sessions 和 sys.dm_exec_requests，输出格式老旧，不包含等待资源详情，无法识别锁类型（如 KEY、PAGE、OBJECT）
sp_lock 底层调用已废弃的 sys.syslockinfo，返回结果不稳定，在高并发下常漏锁、错行，且不支持现代锁粒度（如意向锁、键范围锁）
二者均不输出死锁图结构，也无法关联系统进程与应用连接字符串（host_name、program_name 需从 sys.dm_exec_sessions 显式查）

替代方案很简单：直接查 DMV。例如查所有阻塞源头：

SELECT 
  s.session_id,
  s.host_name,
  s.program_name,
  s.login_name,
  r.status,
  r.command,
  r.wait_type,
  r.wait_time
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.session_id IN (
  SELECT DISTINCT blocking_session_id 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id  0
)

注意过滤掉 status = 'sleeping' 的会话（可能是未提交事务的空闲连接）
wait_type 为 LCK_M_XX 类型才表明真正在等锁；若为 ASYNC_NETWORK_IO 或 PAGEIOLATCH_XX，阻塞根源不在锁本身

死锁长期留存必须建专用 XEvent 会话

system_health 是轻量兜底方案，但环形缓冲区不可控，容易被覆盖。生产环境必须建专用会话，把 xml_deadlock_report 输出到文件：

目标类型选 event_file，路径设为独立磁盘（避免与数据文件争 I/O）
启用 MAX_FILE_SIZE = 100 MB 和 MAX_ROLLOVER_FILES = 10 实现自动滚动
添加 action 如 sqlserver.database_name、sqlserver.client_app_name，方便按业务分类分析

创建语句核心片段（省略完整 DDL）：

CREATE EVENT SESSION [capture_deadlocks] ON SERVER 
ADD EVENT sqlserver.xml_deadlock_report(
  ACTION(sqlserver.database_name, sqlserver.client_app_name)
)
ADD TARGET package0.event_file(
  SET filename=N'D:\XEvents\deadlocks.xel',
      max_file_size=(100),
      max_rollover_files=(10)
)