SQL Server 阻塞与死锁排查 100 条命令

DBA100

100 条命令系列文章专栏

SQL Server 阻塞与死锁排查 100 条命令

前言

SQL Server 查询卡住时，先确定是锁等待，还是 CPU、I/O 等资源等待。锁等待要沿着 blocking_session_id 找到最上游会话；最上游可能已经空闲，但事务没提交。死锁则是另一回事：两边互等，数据库会选择一方回滚，需要看死锁图里的资源和语句顺序。

这篇从当前等待链查到长事务，再接死锁证据。值班时按连接号翻查即可；建议收藏，也欢迎分享给负责事务代码的同事。

SQL Server 锁等待与死锁关系示意图 SQL Server 锁等待与死锁关系示意图

示例采用 SQL Server 2022。以下 DMV 多数需要 VIEW SERVER PERFORMANCE STATE。现场视图随时变化，终止会话之前要确认持锁事务的业务归属和回滚影响。

先确认是不是锁等待

1. 查看正在执行的请求和阻塞会话

SQL

1SELECT session_id, request_id, status,
2       blocking_session_id, wait_type, wait_time,
3       wait_resource, database_id
4FROM sys.dm_exec_requests
5WHERE session_id <> @@SPID
6ORDER BY wait_time DESC;

wait_time 是当前等待的毫秒数，不是整条 SQL 的运行时间。blocking_session_id 有时是负值，不能一律当成可终止的客户端连接；先看 wait_type。

2. 只列出正被其他会话挡住的请求

SQL

1SELECT session_id AS waiting_session,
2       blocking_session_id, wait_type,
3       wait_time, wait_resource
4FROM sys.dm_exec_requests
5WHERE blocking_session_id > 0
6ORDER BY wait_time DESC;

正数会话号可以继续追。这里展示的是请求级等待；并行查询的不同任务可能等不同资源，下一条要看任务级视图。

3. 查看任务级阻塞与等待时长

SQL

1SELECT session_id, exec_context_id,
2       blocking_session_id, wait_type,
3       wait_duration_ms, resource_description
4FROM sys.dm_os_waiting_tasks
5WHERE blocking_session_id > 0
6ORDER BY wait_duration_ms DESC;

一条并行请求可能出现多行。任务级 wait_duration_ms 和第 1 条的请求级 wait_time 不必完全相同；分析时保留会话、任务编号。

4. 查看阻塞会话的登录名、来源和事务数

SQL

1SELECT s.session_id, s.status, s.login_name,
2       s.host_name, s.program_name,
3       s.open_transaction_count,
4       s.last_request_start_time, s.last_request_end_time
5FROM sys.dm_exec_sessions AS s
6WHERE s.session_id IN
7  (SELECT blocking_session_id
8   FROM sys.dm_exec_requests
9   WHERE blocking_session_id > 0);

最上游会话可能显示 sleeping，但 open_transaction_count 大于零。先联系应用确认提交点，不要只因为它当前没 SQL 就认为可以忽略。

5. 取出等待请求当前正在执行的 SQL

SQL

1SELECT r.session_id, r.blocking_session_id,
2       r.wait_type, t.text AS batch_text
3FROM sys.dm_exec_requests AS r
4CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
5WHERE r.blocking_session_id > 0;

batch_text 可能包含多条语句；要锁定当前语句，还需用 statement_start_offset、statement_end_offset 截取。这里先保存整批文本和连接号。

找持锁事务

6. 查看当前等待授予的锁请求

SQL

1SELECT request_session_id, resource_database_id,
2       resource_type, resource_associated_entity_id,
3       request_mode, request_status
4FROM sys.dm_tran_locks
5WHERE request_status = 'WAIT';

锁请求结果是实时截面。resource_associated_entity_id 随资源类型而含义不同，不能直接当作表的 object_id。

7. 查看一个会话持有和等待的锁

SQL

1SELECT request_session_id, resource_database_id,
2       resource_type, resource_description,
3       request_mode, request_status,
4       request_owner_type, request_owner_id
5FROM sys.dm_tran_locks
6WHERE request_session_id = 52
7ORDER BY resource_type, request_status;

把 52 换成现场连接号。同一会话可能有多个事务或锁所有者；看 request_owner_type、request_owner_id，别简单地把所有行当成一个事务。

8. 查会话上的活跃事务开始时间

SQL

1SELECT st.session_id, st.transaction_id,
2       at.transaction_begin_time, at.transaction_type,
3       at.transaction_state
4FROM sys.dm_tran_session_transactions AS st
5JOIN sys.dm_tran_active_transactions AS at
6  ON at.transaction_id = st.transaction_id
7WHERE st.session_id = 52
8ORDER BY at.transaction_begin_time;

开始早的事务值得追，但它未必就是这次阻塞的唯一原因。分布式或绑定事务可能关联多个会话；先把事务号与锁所有者对齐。

9. 查看空闲持锁会话上次提交的批处理

SQL

1SELECT s.session_id, s.status, s.open_transaction_count,
2       ib.event_info AS last_batch
3FROM sys.dm_exec_sessions AS s
4CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
5WHERE s.session_id = 52;

输入缓冲区给的是该连接提交的批处理文本，不一定是当前仍持锁的确切语句；结合第 8 条事务开始时间和应用日志判断。

10. 确认 system_health 是否正在采集

SQL

1SELECT name, create_time
2FROM sys.dm_xe_sessions
3WHERE name = 'system_health';

SQL Server 默认的 system_health 会采集死锁图。当前会话不存在时，先确认它是否被停用；不应为排查而随意修改这个系统会话。

从等待者追到最上游会话

11. 统计每个会话当前挡住多少请求

SQL

1SELECT blocking_session_id AS blocker_session_id,
2       COUNT(*) AS blocked_requests,
3       MAX(wait_time) AS longest_wait_ms
4FROM sys.dm_exec_requests
5WHERE blocking_session_id > 0
6GROUP BY blocking_session_id
7ORDER BY blocked_requests DESC, longest_wait_ms DESC;

先找影响面大的连接，再看最长等待多久。计数是请求数，不是被影响的用户数；同一个应用的多个请求可能在等待，不能只按数量决定要终止谁。

12. 找目前没有再被别的请求挡住的候选链头

SQL

1WITH edges AS (
2  SELECT session_id AS waiting_session_id,
3         blocking_session_id AS blocker_session_id
4  FROM sys.dm_exec_requests
5  WHERE blocking_session_id > 0
6)
7SELECT DISTINCT e.blocker_session_id
8FROM edges AS e
9WHERE NOT EXISTS
10  (SELECT 1 FROM edges AS x
11   WHERE x.waiting_session_id = e.blocker_session_id)
12ORDER BY e.blocker_session_id;

这是当前请求级视图里的候选链头，不保证它没有任务级等待，也不保证就是唯一元凶。最上游可能已经 sleeping、事务却没结束；拿结果去第 4、8 条核对。

13. 找正在阻塞别人但自己已空闲的连接

SQL

1SELECT s.session_id, s.status,
2       s.open_transaction_count,
3       s.login_name, s.program_name,
4       s.last_request_end_time
5FROM sys.dm_exec_sessions AS s
6WHERE s.status = 'sleeping'
7  AND s.open_transaction_count > 0
8  AND EXISTS
9    (SELECT 1 FROM sys.dm_exec_requests AS r
10     WHERE r.blocking_session_id = s.session_id);

这往往指向应用事务边界：上一条请求结束，连接却没提交。先查连接池、调用链与事务开始时间；sleeping 不是“可以安全 KILL”的标志。

14. 截出当前等待请求所在批次的语句

SQL

1SELECT r.session_id, r.blocking_session_id,
2       r.wait_type,
3       SUBSTRING(st.text,
4                 r.statement_start_offset / 2 + 1,
5                 (CASE r.statement_end_offset
6                    WHEN -1 THEN DATALENGTH(st.text)
7                    ELSE r.statement_end_offset
8                  END - r.statement_start_offset) / 2 + 1)
9         AS current_statement
10FROM sys.dm_exec_requests AS r
11CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
12WHERE r.blocking_session_id > 0;

SQL Server 的语句偏移量按字节记录，nvarchar 截取要除以 2。这样比第 5 条的整批 SQL 更接近当前语句；文本仍可能因权限、计划状态或动态 SQL 而不完整，现场参数还要从应用补。

15. 同时查看等待授予和等待转换的锁

SQL

1SELECT request_session_id, resource_database_id,
2       resource_type, resource_description,
3       request_mode, request_status,
4       resource_associated_entity_id
5FROM sys.dm_tran_locks
6WHERE request_status IN ('WAIT', 'CONVERT')
7ORDER BY resource_database_id, resource_type,
8         request_session_id;

CONVERT 表示会话已拿着一种锁，正在申请升级；只筛 WAIT 可能漏掉这类场景。锁 DMV 是实时截面，资源 ID 的意义取决于 resource_type，不要先把所有数字都当成表 ID。

16. 把 OBJECT 锁映射到数据库对象

SQL

1SELECT l.request_session_id,
2       DB_NAME(l.resource_database_id) AS database_name,
3       OBJECT_NAME(l.resource_associated_entity_id,
4                   l.resource_database_id) AS object_name,
5       l.request_mode, l.request_status
6FROM sys.dm_tran_locks AS l
7WHERE l.resource_type = 'OBJECT'
8  AND l.request_status IN ('WAIT', 'CONVERT');

只有 OBJECT 资源的关联 ID 才按对象 ID 解释。对象名为空时先看数据库上下文和权限；不要拿这条映射办法去解释 KEY、PAGE 或 XACT 锁。

17. 将 KEY 锁的 HoBT ID 对到表和索引

SQL

1USE appdb;
2SELECT l.request_session_id, l.request_mode,
3       l.request_status,
4       OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
5       OBJECT_NAME(p.object_id) AS table_name,
6       i.name AS index_name, p.partition_number
7FROM sys.dm_tran_locks AS l
8JOIN sys.partitions AS p
9  ON p.hobt_id = l.resource_associated_entity_id
10JOIN sys.indexes AS i
11  ON i.object_id = p.object_id
12 AND i.index_id = p.index_id
13WHERE l.resource_database_id = DB_ID()
14  AND l.resource_type = 'KEY'
15  AND l.request_status IN ('WAIT', 'CONVERT');

对 KEY 资源，关联 ID 是 HoBT ID，可映射到索引和分区；这仍不能直接给出被锁行的主键。先确认会话 SQL 的筛选条件和事务顺序，再从业务数据定位行。

18. 单独查看应用锁请求

SQL

1SELECT request_session_id,
2       DB_NAME(resource_database_id) AS database_name,
3       resource_description,
4       request_mode, request_status,
5       request_owner_type
6FROM sys.dm_tran_locks
7WHERE resource_type = 'APPLICATION'
8  AND request_status IN ('WAIT', 'CONVERT');

sp_getapplock 产生的应用锁不对应普通表行。资源名由应用约定，得回查代码中的锁名和所有者类型；如果把它当作索引冲突排查，方向就错了。

19. 查看候选阻塞者所在库的事务日志用量

SQL

1SELECT st.session_id, dt.database_id,
2       dt.database_transaction_begin_time,
3       dt.database_transaction_log_bytes_used
4         / 1048576.0 AS log_used_mb
5FROM sys.dm_tran_session_transactions AS st
6JOIN sys.dm_tran_database_transactions AS dt
7  ON dt.transaction_id = st.transaction_id
8WHERE st.session_id = 52
9ORDER BY log_used_mb DESC;

把 52 换成候选链头。日志记录量有助于估计回滚成本，但不等于回滚耗时；事务可能跨库，不能只看当前业务库的一行。处置前要联系业务方确认是否仍在提交路径上。

20. 查看实例累计锁等待的统计起点

SQL

1SELECT wait_type, waiting_tasks_count,
2       wait_time_ms, signal_wait_time_ms
3FROM sys.dm_os_wait_stats
4WHERE wait_type LIKE 'LCK_M_%'
5ORDER BY wait_time_ms DESC;

LCK_M_% 是实例级累计等待，不能直接说明哪张表或哪段 SQL 正在锁住谁。看近期变化必须留两次取样并核对统计是否被清零或实例是否重启；当前阻塞链仍按第 1—19 条查。

死锁发生后看图，不靠猜

21. 核对 system_health 当前有哪些保存目标

SQL

1SELECT xs.name, xt.target_name
2FROM sys.dm_xe_sessions AS xs
3JOIN sys.dm_xe_session_targets AS xt
4  ON xt.event_session_address = xs.address
5WHERE xs.name = N'system_health';

通常有 ring_buffer 和 event_file。ring buffer 容量有限，旧事件可能被覆盖；event file 也会滚动保留。先看目标是否存在，再说“这段时间没有死锁图”。

22. 从 ring buffer 列出最近死锁事件

SQL

1SELECT xdr.value('@timestamp', 'datetime')
2         AS deadlock_time_utc,
3       xdr.query('.') AS event_xml
4FROM (SELECT CAST(xt.target_data AS xml) AS target_data
5      FROM sys.dm_xe_session_targets AS xt
6      JOIN sys.dm_xe_sessions AS xs
7        ON xs.address = xt.event_session_address
8      WHERE xs.name = N'system_health'
9        AND xt.target_name = N'ring_buffer') AS rb
10CROSS APPLY rb.target_data.nodes
11  ('RingBufferTarget/event[@name="xml_deadlock_report"]')
12  AS events(xdr)
13ORDER BY deadlock_time_utc DESC;

这是 Microsoft 官方示例的读取方式。时间戳是 UTC，和应用告警时间对照时先换算时区；ring buffer 没记录不等于没发生，继续看 event file。

23. 只抽出死锁图主体

SQL

1SELECT xdr.value('@timestamp', 'datetime')
2         AS deadlock_time_utc,
3       xdr.query
4         ('(data[@name="xml_report"]/value/deadlock)[1]')
5         AS deadlock_graph
6FROM (SELECT CAST(xt.target_data AS xml) AS target_data
7      FROM sys.dm_xe_session_targets AS xt
8      JOIN sys.dm_xe_sessions AS xs
9        ON xs.address = xt.event_session_address
10      WHERE xs.name = N'system_health'
11        AND xt.target_name = N'ring_buffer') AS rb
12CROSS APPLY rb.target_data.nodes
13  ('RingBufferTarget/event[@name="xml_deadlock_report"]')
14  AS events(xdr)
15ORDER BY deadlock_time_utc DESC;

图主体有 victim-list、process-list、resource-list。先保存完整 XML，再抽具体字段；只截一张 SSMS 图容易丢掉 SQL 文本、事务开始时间和锁模式。

24. 列出死锁图里每个参与会话

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT p.value('@id', 'nvarchar(100)') AS process_id,
16       p.value('@spid', 'int') AS session_id,
17       p.value('@waitresource', 'nvarchar(4000)')
18         AS wait_resource,
19       p.value('@isolationlevel', 'nvarchar(100)')
20         AS isolation_level
21FROM deadlocks AS d
22CROSS APPLY d.graph_xml.nodes
23  ('/deadlock/process-list/process') AS processes(p);

process_id 是图内标识，session_id 才是 SQL Server 会话号；图中的会话现在可能已经结束，别拿它去直接 KILL。还要看每个进程的 executionStack、事务开始时间和访问顺序。

25. 单独取出死锁图的资源列表

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT r.query('.') AS resource_xml
16FROM deadlocks AS d
17CROSS APPLY d.graph_xml.nodes
18  ('/deadlock/resource-list/*') AS resources(r);

每个资源节点能看到持有者和等待者。KEY、PAGE、OBJECT、应用锁等资源不能用同一套 ID 映射；第 16—18 条只适用于各自资源类型，死锁 XML 的对象信息还要按事件里的数据库核对。

26. 统计 ring buffer 当前保留了几张死锁图

SQL

1SELECT COUNT(*) AS retained_deadlock_events
2FROM (SELECT CAST(xt.target_data AS xml) AS target_data
3      FROM sys.dm_xe_session_targets AS xt
4      JOIN sys.dm_xe_sessions AS xs
5        ON xs.address = xt.event_session_address
6      WHERE xs.name = N'system_health'
7        AND xt.target_name = N'ring_buffer') AS rb
8CROSS APPLY rb.target_data.nodes
9  ('RingBufferTarget/event[@name="xml_deadlock_report"]')
10  AS events(xdr);

计数是目前还在内存目标里的事件，不是服务器自启动以来的死锁总次数。环形缓冲覆盖后会变少，实例重启也会清空；事故复盘应优先保存图并检查文件目标。

27. 查看 system_health 文件目标的信息

SQL

1SELECT xt.target_name,
2       CAST(xt.target_data AS xml) AS event_file_target
3FROM sys.dm_xe_session_targets AS xt
4JOIN sys.dm_xe_sessions AS xs
5  ON xs.address = xt.event_session_address
6WHERE xs.name = N'system_health'
7  AND xt.target_name = N'event_file';

从 XML 中核对文件位置和目标状态，再用下一条读取滚动的 .xel 文件。实际目录随实例安装位置变化；不要硬套另一台服务器的路径。

28. 从 event file 查最近一天的死锁图

SQL

1SELECT timestamp_utc,
2       CAST(event_data AS xml) AS event_xml
3FROM sys.fn_xe_file_target_read_file
4  (N'system_health*.xel', NULL, NULL, NULL)
5WHERE object_name = N'xml_deadlock_report'
6  AND timestamp_utc > DATEADD(DAY, -1, GETUTCDATE())
7ORDER BY timestamp_utc DESC;

默认文件目录可用通配模式；非默认目录或权限受限时，按第 27 条的文件目标改成完整路径。文件保留量有限，故障时要及时留存原始 XML。文件没结果不能替代应用 1205 错误和其他监控证据。

29. 核对目标数据库的行版本隔离设置

SQL

1SELECT name, is_read_committed_snapshot_on,
2       snapshot_isolation_state_desc
3FROM sys.databases
4WHERE name = N'appdb';

死锁图里读写互等时，先看库的隔离配置和进程的实际 isolationlevel。开启 RCSI 可能减少某些读写阻塞，但不会消灭所有写写死锁；这是数据库级变更，不能在事故现场仅凭一张图直接改。

30. 核对涉事表的锁升级配置

SQL

1USE appdb;
2SELECT SCHEMA_NAME(schema_id) AS schema_name,
3       name AS table_name, lock_escalation_desc
4FROM sys.tables
5WHERE name IN (N'orders', N'customers');

TABLE、AUTO、DISABLE 会影响锁升级方式，但配置值不是“这次已经发生升级”的证据。结合死锁资源列表、事务访问顺序和持锁数量判断；禁用锁升级还可能让实例保留大量细粒度锁，不能当作通用修复。

31. 从死锁图确认被回滚的会话

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT d.graph_xml.value
16         ('(/deadlock/victim-list/victimProcess/@id)[1]',
17          'nvarchar(100)') AS victim_process_id,
18       p.value('@spid', 'int') AS victim_session_id,
19       p.value('@clientapp', 'nvarchar(256)') AS client_app,
20       p.value('@waitresource', 'nvarchar(4000)') AS wait_resource
21FROM deadlocks AS d
22CROSS APPLY d.graph_xml.nodes
23  ('/deadlock/process-list/process') AS processes(p)
24WHERE p.value('@id', 'nvarchar(100)') =
25      d.graph_xml.value
26        ('(/deadlock/victim-list/victimProcess/@id)[1]',
27         'nvarchar(100)');

victimProcess/@id 是死锁图里的进程标识，需与 process-list 对上才能得到 SPID。它记录的是当时被选中回滚的事务；事故发生后该 SPID 可能已复用，不能直接拿结果去操作当前连接。应用侧应能对应到 1205 错误和重试记录。

32. 比较死锁参与者的事务与等待信息

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT p.value('@spid', 'int') AS session_id,
16       p.value('@lasttranstarted', 'datetime')
17         AS transaction_started,
18       p.value('@waittime', 'int') AS wait_ms,
19       p.value('@logused', 'bigint') AS log_used_bytes,
20       p.value('@lockMode', 'nvarchar(30)') AS requested_mode,
21       p.value('@clientapp', 'nvarchar(256)') AS client_app
22FROM deadlocks AS d
23CROSS APPLY d.graph_xml.nodes
24  ('/deadlock/process-list/process') AS processes(p);

看双方事务何时开始、等了多久、使用了多少日志，而不是只看谁先被回滚。logused 是死锁图中的日志字节数，不能据此精确推算回滚耗时；更重要的是对照双方事务的访问顺序和锁模式。

33. 从死锁图抽出真正执行到的语句帧

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT p.value('@spid', 'int') AS session_id,
16       f.value('@procname', 'nvarchar(512)') AS proc_name,
17       f.value('@line', 'int') AS line_number,
18       f.value('text()[1]', 'nvarchar(4000)') AS statement_text
19FROM deadlocks AS d
20CROSS APPLY d.graph_xml.nodes
21  ('/deadlock/process-list/process') AS processes(p)
22CROSS APPLY p.nodes('executionStack/frame') AS frames(f);

executionStack 比只看会话最后一个 batch 更接近争锁时的执行位置。帧文本也可能显示 unknown、截断或只有存储过程位置；遇到这种情况用过程版本、行号和应用参数补齐，不能凭一行残缺文本判断 SQL 写法。

34. 查看死锁参与者提交的完整批次文本

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT p.value('@spid', 'int') AS session_id,
16       p.value('(inputbuf/text())[1]', 'nvarchar(4000)')
17         AS input_batch
18FROM deadlocks AS d
19CROSS APPLY d.graph_xml.nodes
20  ('/deadlock/process-list/process') AS processes(p);

inputbuf 可以包含整个调用批次，未必就是触发死锁的具体语句。与第 33 条的执行帧一起读，才能分清存储过程入口、当前语句和前面已持有锁的语句。文本里可能有业务参数，留存时要按生产数据处理。

35. 把资源的持有者和等待者放在一起

SQL

1WITH deadlocks AS (
2  SELECT xdr.query
3    ('(data[@name="xml_report"]/value/deadlock)[1]')
4      AS graph_xml
5  FROM (SELECT CAST(xt.target_data AS xml) AS target_data
6        FROM sys.dm_xe_session_targets AS xt
7        JOIN sys.dm_xe_sessions AS xs
8          ON xs.address = xt.event_session_address
9        WHERE xs.name = N'system_health'
10          AND xt.target_name = N'ring_buffer') AS rb
11  CROSS APPLY rb.target_data.nodes
12    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
13    AS events(xdr)
14)
15SELECT r.value('@objectname', 'nvarchar(512)') AS object_name,
16       r.value('@indexname', 'nvarchar(512)') AS index_name,
17       r.query('owner-list') AS owner_xml,
18       r.query('waiter-list') AS waiter_xml
19FROM deadlocks AS d
20CROSS APPLY d.graph_xml.nodes
21  ('/deadlock/resource-list/*') AS resources(r);

先从同一资源的 owner-list、waiter-list 看清“谁持有、谁在等”，再与第 24 条的进程 ID 对上。KEY 锁可能带对象和索引名，APPLICATION、XACT 等资源的属性不同，这两列为空时继续读完整资源 XML，不要说“图里没对象”。

当前阻塞还在时回到锁请求

36. 区分等待新锁与等待锁转换

SQL

1SELECT request_session_id, resource_database_id,
2       resource_type, resource_description,
3       request_mode, request_status,
4       request_owner_type
5FROM sys.dm_tran_locks
6WHERE request_status IN (N'WAIT', N'CONVERT')
7ORDER BY request_session_id, resource_type;

WAIT 表示新请求还没拿到锁；CONVERT 表示已持有一个模式、正在等升级到另一个模式。两者都可能形成阻塞，但处理前先按资源类型和会话事务核对，不要把同一资源上的所有持锁者都当成冲突方。SQL Server 2022 查看全实例锁状态需要相应性能状态权限。

37. 查看被阻塞会话的任务级锁等待

SQL

1SELECT session_id, exec_context_id, blocking_session_id,
2       wait_type, wait_duration_ms, resource_description
3FROM sys.dm_os_waiting_tasks
4WHERE session_id = 57
5  AND wait_type LIKE N'LCK_M_%'
6ORDER BY wait_duration_ms DESC;

把第 1 条找到的会话号代入。并行请求可能有多个任务在等锁，因此一行不是一个独立事务；blocking_session_id 也可能是负数，不能直接拿去 KILL。

38. 按库统计当前未授予的锁请求

SQL

1SELECT DB_NAME(resource_database_id) AS database_name,
2       request_status, COUNT(*) AS lock_requests
3FROM sys.dm_tran_locks
4WHERE request_status IN (N'WAIT', N'CONVERT')
5GROUP BY resource_database_id, request_status
6ORDER BY lock_requests DESC;

这是当前瞬时请求数，不是被阻塞的用户数，也不是过去一小时的锁等待量。先找到异常数据库，再按会话和对象查具体冲突。

39. 看等待中的锁模式和资源类型

SQL

1SELECT resource_type, request_mode, request_status,
2       COUNT(*) AS lock_requests
3FROM sys.dm_tran_locks
4WHERE request_status IN (N'WAIT', N'CONVERT')
5GROUP BY resource_type, request_mode, request_status
6ORDER BY lock_requests DESC;

例如 KEY 上大量 X 请求和 OBJECT 上的 Sch-M 请求，排查方向不一样。这条只能看到请求的模式；要判断谁挡住它，仍要结合第 7、16、17 条和事务开始时间。

40. 核对候选阻塞者的锁归属

SQL

1SELECT request_session_id, request_owner_type,
2       request_owner_id, resource_type,
3       request_mode, request_status
4FROM sys.dm_tran_locks
5WHERE request_session_id = 56
6ORDER BY request_owner_type, request_owner_id,
7         resource_type, request_mode;

同一个会话上的锁不一定全由同一个事务持有。request_owner_type = TRANSACTION 时，request_owner_id 是事务 ID；排查连接复用、MARS 或应用锁时，先认清锁的归属，再决定是否处理会话。

41. 把等待任务对到具体锁请求

SQL

1SELECT wt.session_id AS waiting_session_id,
2       wt.blocking_session_id, wt.wait_duration_ms,
3       lk.resource_database_id, lk.resource_type,
4       lk.resource_associated_entity_id,
5       lk.request_mode, lk.request_status
6FROM sys.dm_os_waiting_tasks AS wt
7JOIN sys.dm_tran_locks AS lk
8  ON lk.lock_owner_address = wt.resource_address
9WHERE wt.wait_type LIKE N'LCK_M_%'
10ORDER BY wt.wait_duration_ms DESC;

微软文档给出了 lock_owner_address 与等待任务 resource_address 的对应方法。这比只看 wait_resource 字符串容易核对资源类型，但结果仍是瞬时截面；阻塞解除后再查可能一行也没有。

42. 核对参与会话的隔离级别和超时设置

SQL

1SELECT session_id, transaction_isolation_level,
2       lock_timeout, deadlock_priority,
3       open_transaction_count
4FROM sys.dm_exec_sessions
5WHERE session_id IN (56, 57)
6ORDER BY session_id;

transaction_isolation_level 返回数字：2 为 Read Committed，4 为 Serializable，5 为 Snapshot。lock_timeout 单位是毫秒，-1 表示一直等待；会话级设置会改变现场表现，别只检查数据库的 RCSI 选项。

43. 比对被阻塞请求报告的等待资源

SQL

1SELECT session_id, blocking_session_id,
2       wait_type, wait_time, wait_resource,
3       transaction_id, open_transaction_count
4FROM sys.dm_exec_requests
5WHERE session_id = 57;

把这条的 wait_resource 与第 41 条的锁资源、以及第 16、17 条的对象映射对起来。wait_time 也是当前请求的现场读数；若请求已重试或换了等待点，前后两次截图未必指向同一把锁。

44. 按任务统计某个会话挡住了多少等待

SQL

1SELECT blocking_session_id,
2       COUNT(*) AS waiting_tasks,
3       COUNT(DISTINCT session_id) AS waiting_sessions,
4       MAX(wait_duration_ms) AS longest_wait_ms
5FROM sys.dm_os_waiting_tasks
6WHERE blocking_session_id = 56
7  AND wait_type LIKE N'LCK_M_%'
8GROUP BY blocking_session_id;

第 11 条按请求统计，这里按任务统计。并行 SQL 可能让一个会话出现多个等待任务，所以 waiting_tasks 和 waiting_sessions 要分开读；它们都不是受影响的业务交易数。

阻塞已经消失，查 Query Store 的等待记录

以下命令在涉事业务库执行。Query Store 要已启用并采集等待统计；它记录的是计划和时间段上的汇总，不能代替第 22—35 条的死锁图，也找不回每一次持锁会话。

45. 核对 Query Store 是否在采集等待

SQL

1SELECT actual_state_desc, query_capture_mode_desc,
2       wait_stats_capture_mode_desc, readonly_reason
3FROM sys.database_query_store_options;

wait_stats_capture_mode_desc 为 OFF 时，这一节的历史锁等待可能没有记录。实际状态是 READ_ONLY 时仍可看旧数据，但新语句可能无法继续采集；先弄清事故发生时的配置，别拿现在的开关推断过去。

46. 查看保留了哪些统计时间段

SQL

1SELECT TOP (30) runtime_stats_interval_id,
2       start_time, end_time
3FROM sys.query_store_runtime_stats_interval
4ORDER BY start_time DESC;

事故时间若早于最老时间段，Query Store 已无法提供那段统计。时间段可能是小时级，不能从它推出“锁恰好发生在 10:03”；还要对业务报错、超时和死锁事件时间。

47. 找最近一天 Lock 类等待较高的计划

SQL

1SELECT TOP (20) ws.plan_id,
2       SUM(ws.total_query_wait_time_ms) AS lock_wait_ms
3FROM sys.query_store_wait_stats AS ws
4JOIN sys.query_store_runtime_stats_interval AS i
5  ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
6WHERE ws.wait_category = 3
7  AND i.start_time >= DATEADD(day, -1, SYSDATETIMEOFFSET())
8GROUP BY ws.plan_id
9ORDER BY lock_wait_ms DESC;

类别 3 是 Lock，对应 LCK_M_%。这是已采集计划在时间段内的累计等待，不是持锁者排行；同一 SQL 可能有多个计划，先记下 plan_id，再查语句来源。

48. 将锁等待计划对到 SQL 文本

SQL

1WITH lock_wait AS (
2  SELECT plan_id,
3         SUM(total_query_wait_time_ms) AS lock_wait_ms
4  FROM sys.query_store_wait_stats
5  WHERE wait_category = 3
6  GROUP BY plan_id
7)
8SELECT TOP (20) p.query_id, p.plan_id,
9       LEFT(t.query_sql_text, 300) AS query_text,
10       lw.lock_wait_ms
11FROM lock_wait AS lw
12JOIN sys.query_store_plan AS p ON p.plan_id = lw.plan_id
13JOIN sys.query_store_query AS q ON q.query_id = p.query_id
14JOIN sys.query_store_query_text AS t
15  ON t.query_text_id = q.query_text_id
16ORDER BY lw.lock_wait_ms DESC;

这条不设时间窗，排名可能由很久以前的负载决定。用第 47 条的 plan_id 和事故时间收窄后再读全文；若文本涉及敏感信息，按现场日志权限处理。

49. 查看某条 Query Store 查询的锁等待时间段

SQL

1-- 将 12345 换成第 48 条找到的 query_id
2SELECT i.start_time, i.end_time, p.plan_id,
3       SUM(ws.total_query_wait_time_ms) AS lock_wait_ms
4FROM sys.query_store_wait_stats AS ws
5JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id
6JOIN sys.query_store_runtime_stats_interval AS i
7  ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
8WHERE p.query_id = 12345
9  AND ws.wait_category = 3
10GROUP BY i.start_time, i.end_time, p.plan_id
11ORDER BY i.start_time DESC, p.plan_id;

按时间段看峰值，才能与应用超时窗口相互印证。最新尚未结束的时间段仍可能增长；若同一计划、时间段和类别出现多行，先汇总，别只取一行当总量。

50. 同一 SQL 是否还在等别的资源

SQL

1SELECT ws.wait_category_desc,
2       SUM(ws.total_query_wait_time_ms) AS wait_ms
3FROM sys.query_store_wait_stats AS ws
4JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id
5WHERE p.query_id = 12345
6GROUP BY ws.wait_category_desc
7ORDER BY wait_ms DESC;

Lock 不是唯一慢因。若 Buffer IO、CPU 或 Worker Thread 类也高，排查要回到对应资源。Query Store 是分类汇总，不能在这里读到具体 LCK_M_X 或堵住它的会话号。

51. 对照同一时间段的执行次数与平均耗时

SQL

1SELECT i.start_time, i.end_time, p.plan_id,
2       SUM(rs.count_executions) AS executions,
3       CAST(SUM(rs.avg_duration * rs.count_executions)
4            / NULLIF(SUM(rs.count_executions), 0) / 1000.0
5            AS decimal(18, 2)) AS weighted_avg_duration_ms
6FROM sys.query_store_runtime_stats AS rs
7JOIN sys.query_store_plan AS p ON p.plan_id = rs.plan_id
8JOIN sys.query_store_runtime_stats_interval AS i
9  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
10WHERE p.query_id = 12345
11  AND rs.execution_type = 0
12GROUP BY i.start_time, i.end_time, p.plan_id
13ORDER BY i.start_time DESC;

avg_duration 单位是微秒，这里除以 1000 转毫秒。次数和平均耗时要与第 49 条同一 plan_id、同一时间段比较；锁等待高但耗时均值没明显升高，可能只是部分执行受影响。

52. 区分正常完成与应用中止的等待

SQL

1SELECT ws.execution_type_desc,
2       SUM(ws.total_query_wait_time_ms) AS lock_wait_ms
3FROM sys.query_store_wait_stats AS ws
4JOIN sys.query_store_plan AS p ON p.plan_id = ws.plan_id
5WHERE p.query_id = 12345
6  AND ws.wait_category = 3
7GROUP BY ws.execution_type_desc
8ORDER BY lock_wait_ms DESC;

Aborted 可能是客户端取消或超时，Exception 是异常中止。不要把中止一律当作 SQL Server 自己选了死锁牺牲者；需要对死锁图和应用异常代码。

53. 查看这条查询保留了哪些计划

SQL

1SELECT plan_id, query_id, is_forced_plan,
2       initial_compile_start_time, last_compile_start_time,
3       last_execution_time
4FROM sys.query_store_plan
5WHERE query_id = 12345
6ORDER BY last_execution_time DESC;

一条 SQL 有多个计划时，第 49 条要按计划分别看。计划变化不自动解释 Lock 类等待：锁主要由事务、访问顺序和并发造成；还要检查当时 SQL、索引、隔离级别与业务调用。

54. 核对 Query Store 的留存和容量

SQL

1SELECT stale_query_threshold_days,
2       current_storage_size_mb, max_storage_size_mb,
3       interval_length_minutes, size_based_cleanup_mode_desc
4FROM sys.database_query_store_options;

超过留存期或发生容量清理，旧语句与计划可能已经没了。当前大小接近上限时，第 45 条的只读原因也要一起看；查不到事故 SQL 并不能证明它从未发生锁等待。

有 blocked process 报告，再看谁等谁

这类报告只有实例已设置有效门槛、且扩展事件会话事先采集时才有。下面只读取既有设置和事件；报告中的 SQL、登录名与主机信息可能敏感，保存事故证据时按现场权限处理。

55. 查看 blocked process 报告门槛

SQL

1SELECT name, value, value_in_use
2FROM sys.configurations
3WHERE name = N'blocked process threshold (s)';

value_in_use=0 表示未生成普通阻塞报告；1—4 秒也不会产生有效报告，门槛至少为 5 秒。调整它属于实例级配置变更，这条只读当前值。

56. 找定义中包含阻塞报告的扩展事件会话

SQL

1SELECT s.name AS session_name, e.name AS event_name
2FROM sys.server_event_sessions AS s
3JOIN sys.server_event_session_events AS e
4  ON e.event_session_id = s.event_session_id
5WHERE e.name = N'blocked_process_report'
6ORDER BY s.name;

有定义还不等于当时在运行。这里找到的会话名要用于后续目标查询；没有结果时，不应继续假设 system_health 已替你记录普通阻塞。

57. 确认阻塞报告会话正在运行

SQL

1SELECT xs.name, xe.event_name
2FROM sys.dm_xe_sessions AS xs
3JOIN sys.dm_xe_session_events AS xe
4  ON xe.event_session_address = xs.address
5WHERE xe.event_name = N'blocked_process_report'
6ORDER BY xs.name;

这个 DMV 只列正在运行的会话；现在运行不代表事故发生时也在运行。事件触发计数并非每个实例默认都会填充，不能拿空值判断没有报告。

58. 查看会话把报告存到哪里

SQL

1-- 用第 56/57 条查到的实际会话名替换
2SELECT xs.name, xt.target_name, xt.bytes_written,
3       xs.dropped_event_count, xs.dropped_buffer_count
4FROM sys.dm_xe_sessions AS xs
5JOIN sys.dm_xe_session_targets AS xt
6  ON xt.event_session_address = xs.address
7WHERE xs.name = N'blocking_watch'
8ORDER BY xt.target_name;

ring_buffer 在内存里容易覆盖；event_file 则要核对路径和文件滚动设置。丢弃计数有不同缓冲策略，两个计数都要看；没查到报告时还要检查文件是否仍在。

59. 数一数 ring buffer 里还保留多少报告

SQL

1SELECT COUNT(*) AS retained_reports
2FROM sys.dm_xe_sessions AS xs
3JOIN sys.dm_xe_session_targets AS xt
4  ON xt.event_session_address = xs.address
5CROSS APPLY (SELECT CAST(xt.target_data AS xml) AS payload) AS rb
6CROSS APPLY rb.payload.nodes
7  ('RingBufferTarget/event[@name="blocked_process_report"]') AS ev(x)
8WHERE xs.name = N'blocking_watch'
9  AND xt.target_name = N'ring_buffer';

这里数的是当前内存目标尚未覆盖的报告，不是阻塞次数。一个持续阻塞可能按门槛反复产生多张报告；实例或事件会话重启后，ring buffer 的旧内容会消失。

60. 取出最近的原始阻塞报告

SQL

1SELECT TOP (20)
2       ev.x.value('(@timestamp)[1]', 'datetime2') AS event_utc,
3       ev.x.query('(data[@name="blocked_process"]/value/
4                     blocked-process-report)[1]') AS report_xml
5FROM sys.dm_xe_sessions AS xs
6JOIN sys.dm_xe_session_targets AS xt
7  ON xt.event_session_address = xs.address
8CROSS APPLY (SELECT CAST(xt.target_data AS xml) AS payload) AS rb
9CROSS APPLY rb.payload.nodes
10  ('RingBufferTarget/event[@name="blocked_process_report"]') AS ev(x)
11WHERE xs.name = N'blocking_watch'
12  AND xt.target_name = N'ring_buffer'
13ORDER BY event_utc DESC;

先保存完整 XML，再抽字段；不同版本或采集方式可能让某些属性为空。事件时间是 UTC，和应用本地时间比对时要换时区。若会话只有文件目标，跳到第 63 条。

61. 从报告读等待者和等待时长

SQL

1WITH reports AS (
2  SELECT ev.x.query('(data[@name="blocked_process"]/value/
3                       blocked-process-report)[1]') AS report_xml
4  FROM sys.dm_xe_sessions AS xs
5  JOIN sys.dm_xe_session_targets AS xt
6    ON xt.event_session_address = xs.address
7  CROSS APPLY (SELECT CAST(xt.target_data AS xml) AS payload) AS rb
8  CROSS APPLY rb.payload.nodes
9    ('RingBufferTarget/event[@name="blocked_process_report"]') AS ev(x)
10  WHERE xs.name = N'blocking_watch'
11    AND xt.target_name = N'ring_buffer'
12)
13SELECT report_xml.value('(/blocked-process-report/blocked-process/
14                          process/@spid)[1]', 'int') AS waiting_spid,
15       report_xml.value('(/blocked-process-report/blocked-process/
16                          process/@waittime)[1]', 'bigint') AS wait_ms,
17       report_xml.value('(/blocked-process-report/blocked-process/
18                          process/@waitresource)[1]', 'nvarchar(256)')
19         AS wait_resource
20FROM reports;

waittime 是这张报告生成时的读数，连续报告不能直接把数值相加。waitresource 仍要和第 16、17、41 条的对象、HoBT 和锁请求对齐。

62. 从同一张报告读阻塞者

SQL

1WITH reports AS (
2  SELECT ev.x.query('(data[@name="blocked_process"]/value/
3                       blocked-process-report)[1]') AS report_xml
4  FROM sys.dm_xe_sessions AS xs
5  JOIN sys.dm_xe_session_targets AS xt
6    ON xt.event_session_address = xs.address
7  CROSS APPLY (SELECT CAST(xt.target_data AS xml) AS payload) AS rb
8  CROSS APPLY rb.payload.nodes
9    ('RingBufferTarget/event[@name="blocked_process_report"]') AS ev(x)
10  WHERE xs.name = N'blocking_watch'
11    AND xt.target_name = N'ring_buffer'
12)
13SELECT report_xml.value('(/blocked-process-report/blocking-process/
14                          process/@spid)[1]', 'int') AS blocking_spid,
15       report_xml.value('(/blocked-process-report/blocking-process/
16                          process/@status)[1]', 'nvarchar(40)') AS status,
17       report_xml.value('(/blocked-process-report/blocking-process/
18                          process/@trancount)[1]', 'int') AS transaction_count,
19       report_xml.value('(/blocked-process-report/blocking-process/
20                          process/@lastbatchcompleted)[1]', 'nvarchar(40)')
21         AS last_batch_completed
22FROM reports;

阻塞者显示 sleeping、事务数不为零时，回到第 8、9、13 条查未提交事务和上次批处理。报告是历史截面，不能根据旧 SPID 直接终止当前连接；SPID 可能已经被复用。

63. 查看既有会话的文件目标路径

SQL

1SELECT CAST(xt.target_data AS xml) AS event_file_target
2FROM sys.dm_xe_sessions AS xs
3JOIN sys.dm_xe_session_targets AS xt
4  ON xt.event_session_address = xs.address
5WHERE xs.name = N'blocking_watch'
6  AND xt.target_name = N'event_file';

取出当前文件路径，再按文件滚动模式读取。若只有 ring buffer、没有 event file，就不能假设磁盘上有历史报告；目标文件的保存周期还要看磁盘空间与事件量。

64. 从事件文件读取阻塞报告

SQL

1-- 替换为第 63 条实际文件目录及文件名前缀
2SELECT TOP (50) timestamp_utc,
3       CAST(event_data AS xml) AS event_xml
4FROM sys.fn_xe_file_target_read_file
5  (N'C:\XE\blocking_watch*.xel', NULL, NULL, NULL)
6WHERE object_name = N'blocked_process_report'
7ORDER BY timestamp_utc DESC;

文件读取保留完整事件和报告，适合事故复盘。文件没命中时，核对会话、门槛、权限、路径与保留周期；不要把“没找到 XML”写成“没有发生阻塞”。

处理长事务之前，把回滚代价看清楚

65. 查看候选阻塞者写了多少日志记录

SQL

1SELECT st.session_id, DB_NAME(dt.database_id) AS database_name,
2       dt.database_transaction_log_record_count,
3       dt.database_transaction_log_bytes_used
4FROM sys.dm_tran_session_transactions AS st
5JOIN sys.dm_tran_database_transactions AS dt
6  ON dt.transaction_id = st.transaction_id
7WHERE st.session_id = 52
8ORDER BY dt.database_transaction_log_bytes_used DESC;

第 19 条按 MB 看事务日志用量，这里再看记录数和跨库分布。记录很多、字节数大时，终止连接可能启动较长回滚；这仍不能换算成准确完成时间。

66. 查看涉事数据库的日志使用比例

SQL

1USE appdb;
2SELECT total_log_size_in_bytes / 1048576.0 AS total_log_mb,
3       used_log_space_in_bytes / 1048576.0 AS used_log_mb,
4       used_log_space_in_percent
5FROM sys.dm_db_log_space_usage;

这是当前数据库日志空间，不是第 65 条那笔事务单独占用的空间。回滚、其他写入和日志备份都可能改变读数；日志接近满时，还要检查自动增长、磁盘剩余量和日志截断状态。

67. 查这个库日志中最老的活跃事务

SQL

1-- sysadmin 或目标库 db_owner
2DBCC OPENTRAN (N'appdb') WITH NO_INFOMSGS;

它返回日志里的最老活跃事务，有助于判断日志截断被谁卡住。最老事务未必是当前锁等待链头；要把 SPID、开始时间与第 8、19 条对齐。

68. 核对阻塞连接最近的网络读写时间

SQL

1SELECT c.session_id, c.connect_time,
2       c.last_read, c.last_write,
3       c.net_transport, s.status,
4       s.open_transaction_count
5FROM sys.dm_exec_connections AS c
6JOIN sys.dm_exec_sessions AS s
7  ON s.session_id = c.session_id
8WHERE c.session_id = 52;

last_read、last_write 是连接上的网络活动时间，不是事务提交时间。空闲连接仍开事务时，结合第 9 条输入缓冲区和应用请求日志查调用链；不能把“网络没动”直接等同于客户端已失联。

69. 再核对连接最近一次 SQL 句柄

SQL

1SELECT c.session_id,
2       LEFT(txt.text, 1000) AS most_recent_batch
3FROM sys.dm_exec_connections AS c
4OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
5WHERE c.session_id = 52;

连接最近的 SQL 句柄与第 9 条的输入缓冲区可交叉核对。它也不保证就是拿锁那条语句：一个事务里可能先修改，再执行无关查询后进入空闲状态。

70. 找占用行版本时间较长的事务

SQL

1SELECT TOP (20) session_id, transaction_id,
2       elapsed_time_seconds, is_snapshot,
3       max_version_chain_traversed
4FROM sys.dm_tran_active_snapshot_database_transactions
5ORDER BY elapsed_time_seconds DESC;

这个 DMV 包含生成或访问行版本的活跃事务，不只包含 SNAPSHOT 读者。长事务可能拖住旧版本清理；若与第 29 条的 RCSI/SNAPSHOT 设置有关，再看版本存储空间。

71. 按数据库查看 tempdb 版本存储用量

SQL

1SELECT DB_NAME(database_id) AS database_name,
2       reserved_page_count,
3       reserved_space_kb / 1024.0 AS version_store_mb
4FROM sys.dm_tran_version_store_space_usage
5ORDER BY reserved_space_kb DESC;

这条按产生版本的数据库汇总 tempdb 空间，不能归因到某一个阻塞者。RCSI 可以减少某些读写锁等待，但不是无成本开关；版本存储持续增长时，要结合第 70 条长事务检查。

72. 查看 tempdb 文件中版本存储和空闲空间

SQL

1USE tempdb;
2SELECT SUM(version_store_reserved_page_count) / 128.0
3         AS version_store_mb,
4       SUM(unallocated_extent_page_count) / 128.0
5         AS unallocated_mb
6FROM sys.dm_db_file_space_usage;

这是 tempdb 文件级汇总，与第 71 条的按业务库归属统计口径不同。unallocated_mb 只包括未分配 extent，不等于磁盘可用空间；文件自动增长还要看所在卷。

73. 查看涉事索引的行锁、页锁配置

SQL

1USE appdb;
2SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
3       t.name AS table_name, i.name AS index_name,
4       i.allow_row_locks, i.allow_page_locks
5FROM sys.tables AS t
6JOIN sys.indexes AS i ON i.object_id = t.object_id
7WHERE t.name = N'orders'
8ORDER BY i.index_id;

如果某个索引禁用了行锁或页锁，它可能改变锁粒度选择；但锁升级、语句扫描范围和事务并发也要看。别因为看到表锁就直接把这两个选项改成 ON。

74. 查看已经启动的 KILL 回滚进度

SQL

1-- 仅在该 SPID 已由授权人员执行 KILL 后查看
2KILL 52 WITH STATUSONLY;

WITH STATUSONLY 只报告此前 KILL 的回滚进度，不会再次终止会话。若回滚已经结束或从未启动，会提示没有状态；不要重复裸 KILL 52，因为旧 SPID 可能已被新连接复用。

PAGE、RID 锁：先认资源，再找对象

75. 列出当前等待的 PAGE 锁

SQL

1SELECT request_session_id, resource_database_id,
2       resource_description,
3       resource_associated_entity_id AS hobt_id,
4       request_mode, request_status
5FROM sys.dm_tran_locks
6WHERE resource_type = N'PAGE'
7  AND request_status IN (N'WAIT', N'CONVERT')
8ORDER BY resource_database_id, request_session_id;

resource_description 给出文件与页号；关联 ID 可能是 HoBT ID，也可能没有可用映射。PAGE 锁与 PAGELATCH_% 等页闩等待不同，先确认等待类型是 LCK_M_%。

76. 用可用的 HoBT ID 映射 PAGE 锁

SQL

1USE appdb;
2SELECT l.request_session_id, l.resource_description,
3       OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
4       OBJECT_NAME(p.object_id) AS table_name,
5       i.name AS index_name, p.partition_number
6FROM sys.dm_tran_locks AS l
7JOIN sys.partitions AS p
8  ON p.hobt_id = l.resource_associated_entity_id
9JOIN sys.indexes AS i
10  ON i.object_id = p.object_id
11 AND i.index_id = p.index_id
12WHERE l.resource_database_id = DB_ID()
13  AND l.resource_type = N'PAGE'
14  AND l.request_status IN (N'WAIT', N'CONVERT');

这条只会显示能提供 HoBT ID 的 PAGE 锁；没有结果不能说页不存在。若第 75 条有 PAGE 等待但映射不到对象，再看请求的 page_resource 和页头。

77. 从当前请求解出 page_resource 的库、文件和页

SQL

1SELECT r.session_id, r.wait_type, r.wait_resource,
2       pr.db_id, pr.file_id, pr.page_id
3FROM sys.dm_exec_requests AS r
4CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pr
5WHERE r.page_resource IS NOT NULL
6  AND r.wait_type LIKE N'LCK_M_%';

SQL Server 2019 起能从 page_resource 解出页位置。这里只看当前请求且有 page_resource 的锁等待；KEY 锁或已经消失的等待不会靠它“补出”页号。

78. 从页头确认目标对象和索引

SQL

1SELECT r.session_id, r.wait_resource,
2       info.object_id, info.index_id, info.partition_id,
3       info.file_id, info.page_id
4FROM sys.dm_exec_requests AS r
5CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS pr
6CROSS APPLY sys.dm_db_page_info
7  (pr.db_id, pr.file_id, pr.page_id, 'LIMITED') AS info
8WHERE r.page_resource IS NOT NULL
9  AND r.wait_type LIKE N'LCK_M_%';

sys.dm_db_page_info 读页头，能给出对象、索引与分区 ID，不会展示页内被锁的具体行。页面可能已经改变，查询需要目标库的性能状态权限；现场结果要和第 77 条同一时刻核对。

79. 列出 RID 行锁的资源位置

SQL

1SELECT request_session_id, resource_database_id,
2       resource_description,
3       resource_associated_entity_id AS hobt_id,
4       request_mode, request_status
5FROM sys.dm_tran_locks
6WHERE resource_type = N'RID'
7  AND request_status IN (N'WAIT', N'CONVERT')
8ORDER BY resource_database_id, request_session_id;

RID 的描述是文件、页和页内行位置，常见于堆表；这不是业务主键。关联 ID 也可能没有可用 HoBT 信息，不能把描述里的行号直接拿去做 WHERE order_id = ...。

80. 尝试把 RID 锁对到堆表

SQL

1USE appdb;
2SELECT l.request_session_id, l.resource_description,
3       OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
4       OBJECT_NAME(p.object_id) AS table_name,
5       p.partition_number
6FROM sys.dm_tran_locks AS l
7JOIN sys.partitions AS p
8  ON p.hobt_id = l.resource_associated_entity_id
9WHERE l.resource_database_id = DB_ID()
10  AND l.resource_type = N'RID'
11  AND p.index_id = 0
12  AND l.request_status IN (N'WAIT', N'CONVERT');

只有资源提供可用 HoBT ID 时才能映射。RID 位置可帮助确定冲突的堆表和页，却不能单靠锁 DMV 恢复完整行内容；实际更新条件还得回查 SQL 与业务日志。

真要中断阻塞者，先留证，再核对回滚

终止会话是最后手段。先找应用负责人确认事务可回滚、连接号没有换人，并保存等待链和事务证据；如果应用能自己提交或回滚，应先按业务流程处理。

81. 留下当前阻塞链的请求快照

SQL

1SELECT r.session_id AS waiting_session_id,
2       r.blocking_session_id, r.wait_type,
3       r.wait_time, r.wait_resource,
4       DB_NAME(r.database_id) AS database_name,
5       s.login_name, s.program_name
6FROM sys.dm_exec_requests AS r
7JOIN sys.dm_exec_sessions AS s
8  ON s.session_id = r.session_id
9WHERE r.blocking_session_id > 0
10ORDER BY r.blocking_session_id, r.wait_time DESC;

处置前保存结果与时间，后面才能对照受影响请求是否恢复。请求随时可能结束或重试；这张快照不是一份完整的业务影响清单。

82. 再确认目标连接仍是候选链头

SQL

1WITH edges AS (
2  SELECT session_id AS waiter, blocking_session_id AS blocker
3  FROM sys.dm_exec_requests
4  WHERE blocking_session_id > 0
5)
6SELECT e.blocker, COUNT(*) AS blocked_requests
7FROM edges AS e
8WHERE e.blocker = 52
9  AND NOT EXISTS
10    (SELECT 1 FROM edges AS parent
11     WHERE parent.waiter = e.blocker)
12GROUP BY e.blocker;

链头必须在操作前重新核对；第 12 条早先查到的 SPID 可能已经变了。无结果时暂停，不能拿旧截图继续执行后面的 KILL 示例。

83. 核对目标连接的身份和连接 ID

SQL

1SELECT s.session_id, c.connection_id,
2       s.login_name, s.host_name, s.program_name,
3       c.connect_time, s.status,
4       s.open_transaction_count
5FROM sys.dm_exec_sessions AS s
6JOIN sys.dm_exec_connections AS c
7  ON c.session_id = s.session_id
8WHERE s.session_id = 52;

记录 connection_id、账号、应用和连接时间，并与业务方给出的连接对上。MARS 可能有多个逻辑连接行；单凭 session_id=52 无法确认它仍是事故开始时那个连接。

84. 记录目标连接的事务号与开始时间

SQL

1SELECT st.session_id, at.transaction_id,
2       at.transaction_begin_time,
3       at.transaction_state,
4       dt.database_id,
5       dt.database_transaction_log_bytes_used
6FROM sys.dm_tran_session_transactions AS st
7JOIN sys.dm_tran_active_transactions AS at
8  ON at.transaction_id = st.transaction_id
9LEFT JOIN sys.dm_tran_database_transactions AS dt
10  ON dt.transaction_id = st.transaction_id
11WHERE st.session_id = 52
12ORDER BY at.transaction_begin_time;

第 8、65 条分别看过事务与日志，这里在处置前留一份同时间点的组合记录。一个事务可跨库，分布式或绑定事务也可能涉及其他会话；不能把结果行数当成事务数。

85. 最后看一次当前 SQL 和输入缓冲区

SQL

1SELECT s.session_id, r.status AS request_status,
2       r.command, r.wait_type,
3       ib.event_info AS last_submitted_batch
4FROM sys.dm_exec_sessions AS s
5LEFT JOIN sys.dm_exec_requests AS r
6  ON r.session_id = s.session_id
7OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
8WHERE s.session_id = 52;

如果目标仍在运行写入或提交，终止后回滚影响可能比空闲事务大。输入缓冲区只给最后一次批处理，不能证明事务里之前没有其他修改；要结合第 84 条与应用工单。

86. 按已批准的处置方案终止目标会话

SQL

1-- 仅在业务确认可回滚，且第 82—85 条刚刚复核通过后执行
2KILL 52;

这一条会中断会话并回滚未提交事务。锁未必立刻释放，大事务可能回滚很久；执行账号也需要相应权限。不要用负的 blocking_session_id 或旧 SPID 代入。

87. 回读正在回滚的请求

SQL

1SELECT session_id, command, status,
2       percent_complete, wait_type, wait_time
3FROM sys.dm_exec_requests
4WHERE session_id = 52;

若有回滚请求，可先确认它仍在执行，再用第 74 条 KILL ... WITH STATUSONLY 查看官方进度报告。这里没有行也不能单独证明业务已恢复，继续查锁等待链。

88. 检查原阻塞者是否还持有锁

SQL

1SELECT request_session_id, resource_database_id,
2       resource_type, request_mode, request_status,
3       COUNT(*) AS lock_count
4FROM sys.dm_tran_locks
5WHERE request_session_id = 52
6GROUP BY request_session_id, resource_database_id,
7         resource_type, request_mode, request_status
8ORDER BY lock_count DESC;

回滚期间仍可能持锁。结果为空时要确认原连接确已结束，防止 SPID 被新连接复用；更可靠的是把第 83 条的连接身份、应用恢复情况一起核对。

89. 查看原等待任务是否仍在等锁

SQL

1SELECT session_id, blocking_session_id,
2       wait_type, wait_duration_ms, resource_description
3FROM sys.dm_os_waiting_tasks
4WHERE wait_type LIKE N'LCK_M_%'
5  AND blocking_session_id = 52
6ORDER BY wait_duration_ms DESC;

这只检查仍由 SPID 52 挡住的任务；如果链头换成其他会话，还需重新跑第 81 条。并行任务可能多行，不能只凭任务数判断业务恢复比例。

90. 核对业务请求是否脱离阻塞状态

SQL

1SELECT session_id, blocking_session_id,
2       status, wait_type, wait_time, command
3FROM sys.dm_exec_requests
4WHERE session_id IN (57, 58, 59)
5ORDER BY session_id;

把这里的会话号换成第 81 条记录的等待者。请求可能已经超时、被应用取消或重试，DMV 无结果不等于业务成功；还要让应用按真实交易做验收。

处置后回读

91. 查看业务库还有多少未授予的锁

SQL

1SELECT request_status, resource_type,
2       request_mode, COUNT(*) AS lock_requests
3FROM sys.dm_tran_locks
4WHERE resource_database_id = DB_ID(N'appdb')
5  AND request_status IN (N'WAIT', N'CONVERT')
6GROUP BY request_status, resource_type, request_mode
7ORDER BY lock_requests DESC;

这是处置后的全库截面。原链消失但仍有其他锁等待，就要查资源和新链头；业务库里暂时没有 WAIT，也不能保证刚超时的应用交易已经成功。

92. 再找当前最上游的阻塞会话

SQL

1WITH edges AS (
2  SELECT session_id AS waiter, blocking_session_id AS blocker
3  FROM sys.dm_exec_requests
4  WHERE blocking_session_id > 0
5)
6SELECT DISTINCT e.blocker AS current_head_blocker
7FROM edges AS e
8WHERE NOT EXISTS
9  (SELECT 1 FROM edges AS parent
10   WHERE parent.waiter = e.blocker)
11ORDER BY e.blocker;

原 SPID 不在结果里只是一个好迹象；如果出现新链头，就按第 4、8、9 条继续查，别把“只剩另一条链”说成事故全部解决。

93. 查看原等待会话现在在做什么

SQL

1SELECT s.session_id, s.status AS session_status,
2       r.status AS request_status,
3       r.blocking_session_id, r.wait_type,
4       r.command
5FROM sys.dm_exec_sessions AS s
6LEFT JOIN sys.dm_exec_requests AS r
7  ON r.session_id = s.session_id
8WHERE s.session_id IN (57, 58, 59)
9ORDER BY s.session_id;

有些原等待会话会变成空闲，有些已经被连接池关闭或重建。会话状态要和应用请求号、连接 ID 对上；旧 SPID 也可能被复用。

94. 核对原阻塞事务是否仍活跃

SQL

1-- 把 987654321 换成第 84 条记录的 transaction_id
2SELECT transaction_id, transaction_begin_time,
3       transaction_state
4FROM sys.dm_tran_active_transactions
5WHERE transaction_id = 987654321;

如果该事务仍在活动，就继续看回滚和锁。没有行说明此刻找不到这笔活跃事务；还要核对事务后果和业务侧重试，不能仅凭 DMV 空集宣告交易成功。

95. 回读最老活跃事务

SQL

1DBCC OPENTRAN (N'appdb') WITH NO_INFOMSGS;

与第 67 条处置前结果对比，看看最老事务是否换了人。DBCC 输出里仍有老事务时，日志截断可能继续受影响；这里需要 sysadmin 或库 owner 权限。

96. 回读业务库日志空间

SQL

1USE appdb;
2SELECT used_log_space_in_percent,
3       used_log_space_in_bytes / 1048576.0 AS used_log_mb
4FROM sys.dm_db_log_space_usage;

回滚完成后日志使用比例未必立即下降，恢复模式、日志备份与其他事务也在影响它。把结果与第 66 条及磁盘空间一起看，不要把百分比变化直接当作回滚进度。

97. 回读版本存储空间

SQL

1SELECT DB_NAME(database_id) AS database_name,
2       reserved_space_kb / 1024.0 AS version_store_mb
3FROM sys.dm_tran_version_store_space_usage
4WHERE database_id = DB_ID(N'appdb');

若此前有长时间的行版本事务，空间回收也可能滞后；与第 70、71 条对照，确认没有新的长期事务持续占用版本。版本空间下降与否不能代替锁等待验收。

98. 看 Query Store 后续时间段的 Lock 等待

SQL

1SELECT TOP (12) i.start_time, i.end_time,
2       SUM(ws.total_query_wait_time_ms) AS lock_wait_ms
3FROM sys.query_store_wait_stats AS ws
4JOIN sys.query_store_runtime_stats_interval AS i
5  ON i.runtime_stats_interval_id = ws.runtime_stats_interval_id
6WHERE ws.wait_category = 3
7  AND i.start_time >= DATEADD(hour, -6, SYSDATETIMEOFFSET())
8GROUP BY i.start_time, i.end_time
9ORDER BY i.start_time DESC;

在涉事业务库执行。最近一个时间段仍在累积，Query Store 也不是秒级报警器；等同口径时间窗结束后再比较，不能拿一个尚未结束的区间与整小时旧数据硬比。

99. 核对处置后有没有新的死锁图

SQL

1SELECT timestamp_utc,
2       CAST(event_data AS xml) AS deadlock_event
3FROM sys.fn_xe_file_target_read_file
4  (N'system_health*.xel', NULL, NULL, NULL)
5WHERE object_name = N'xml_deadlock_report'
6  AND timestamp_utc >= '2026-09-01T10:00:00'
7ORDER BY timestamp_utc DESC;

把 UTC 时间改成这次处置开始时刻，文件路径也按第 27 条核对。若有新图，看参与 SQL 和资源是否还是同一批；没有图不等于所有锁等待都没了。

100. 用已知业务记录验证读请求

SQL

1USE appdb;
2-- 订单号从现场已知记录取得；表和列名按业务库替换
3SELECT order_id, status, updated_at
4FROM dbo.orders
5WHERE order_id = 500001;

最后要回到应用真实访问路径。这里的读查询只能说明这条记录可查；写入、提交、超时重试和用户可见结果仍要由应用负责人按业务交易验收。

总结

锁等待先查正在等谁，再找持锁事务和正在执行的语句。阻塞过去以后，拿死锁图、blocked process 报告或 Query Store 时间段补证据。真的要终止连接，先认清账号、事务和回滚量；操作后看锁有没有释放，也让应用确认交易恢复。

ORA100 DBA100 命令系列海报 ORA100 DBA100 命令系列海报

这套数据库运维专题在 ORA100 · DBA100 持续更新：https://ora100.com/dba100

微信小程序搜索 「三笠的百令册」，需要时也能直接翻到命令。

SQL Server 阻塞与死锁排查 100 条命令 - DBA 学习之路