了解和解决 SQL Server 阻塞问题

项目
11/20/2024

适用于：SQL Server（所有受支持的版本）、Azure SQL 托管实例

原始 KB 数： 224453

目标

本文介绍 SQL Server 中的阻塞，并演示如何对阻塞问题进行疑难解答并解决阻塞问题。

在本文中，术语“连接”指的是数据库的单一登录会话。在许多 DMV 中，每个连接都显示为会话 ID (SPID) 或 session_id。每个这种 SPID 通常都称为一个进程，尽管在通常意义上它不是单独的进程上下文。而每个 SPID 都由服务器资源和数据结构组成，这些资源和数据结构是为来自给定客户端的单一连接请求提供服务所必需的。单个客户端应用程序可能有一个或多个连接。从 SQL Server 的角度来看，单客户端计算机上单客户端应用程序的多个连接与多客户端应用程序或多客户端计算机的多个连接之间没有区别；它们是最小单元。不考虑源客户端，一个连接可以阻塞另一个连接。

注意

本文重点介绍 SQL Server 实例，包括 Azure SQL 托管实例。 有关对 Azure SQL 数据库中阻塞问题进行疑难解答的特定信息，请参阅了解并解决 Azure SQL 数据库阻塞问题。

什么是阻塞

对于采用基于锁的并发机制的任何关系数据库管理系统 (RDBMS) 而言，阻塞是不可避免以及设计使然的特征。如前所述，在 SQL Server 中，当一个会话持有特定资源的锁，第二个 SPID 尝试在同一资源上获取冲突锁类型时，会发生阻塞。通常，第一个 SPID 锁定资源的时间范围很小。当拥有的会话释放锁时，第二个连接就可以自由地获取自己对资源的锁并继续处理。此处所述锁定是正常行为，一天可能会发生多次，不会对系统性能产生明显影响。

查询的持续时间和事务上下文决定其锁的保留时间，从而决定它们对其他查询的影响。如果查询未在事务中执行（并且未使用锁提示），则仅在实际读取 SELECT 语句时（而不是在查询期间）才会在资源上持有 SELECT 语句的锁。对于 INSERT、UPDATE 和 DELETE 语句，在查询过程中会保留锁，以确保数据一致性，并允许在必要时回滚查询。

对于在事务中执行的查询，持有锁的持续时间由查询类型、事务隔离级别以及是否在查询中使用锁提示来确定。有关锁定、锁定提示和事务隔离级别的说明，请参阅以下文章：

当锁定和阻塞持续到某种程度时，会对系统性能产生不利影响，这是由于以下原因之一：

SPID 在释放一组资源之前，会在一段较长的时间内持有这些资源的锁。这种类型的阻塞会随着时间的推移自行解决，但会导致性能下降。
SPID 持有一组资源的锁，并且从不释放它们。这种类型的阻塞不会自行解决，并且会无限期地阻止对受影响资源的访问。

在第一种情况下，局面可能非常不稳定，因为随着时间的推移，不同的 SPID 会阻塞不同的资源，从而会创建一个移动的目标。使用 SQL Server Management Studio 将问题缩小到单个查询，很难解决这些问题。相反，第二种情况会导致一致的状态，更容易诊断。

应用程序和阻塞

当遇到阻塞问题时，可能会倾向于关注服务器端优化和平台问题。然而，只关注数据库可能不会导致解决问题，而且会占用时间和精力，以便更好地检查客户端应用程序及其提交的查询。无论应用程序对正在进行的数据库调用公开了何种级别的可见性，阻塞问题都经常需要检查应用程序提交的准确 SQL 语句，以及应用程序在查询取消、连接管理、获取所有结果行等方面的准确行为。如果开发工具不允许对连接管理、查询取消、查询超时、结果提取等进行显式控制，则阻塞问题可能无法解决。在为 SQL Server 选择应用程序开发工具之前，应仔细研究这种可能性，尤其是对于性能敏感的 OLTP 环境。

在数据库和应用程序的设计和构造阶段，要注意数据库的性能。特别是，应为每个查询计算资源消耗、隔离级别和事务路径长度。每个查询和事务都应尽可能轻量。必须执行良好的连接管理规程，否则，应用程序在用户数较少时可能会显示出可接受的性能，但随着用户数的增加，性能可能会显著降低。

通过适当的应用程序和查询设计，SQL Server 能够在一台服务器上同时支持数千个用户，并且几乎没有阻塞。

对阻塞进行故障排除

无论处于哪种阻塞情况，对锁定进行故障排除的方法都是相同的。这些逻辑分离将决定本文的其余部分。也就是说，要找到头阻塞程序，并确定查询正在执行的操作以及会阻塞的原因。一旦识别出有问题的查询（即是什么长时间持有锁），下一步是分析并确定阻塞发生的原因。弄清楚原因后，就可以通过重新设计查询和事务做出更改。

故障排除步骤：

标识主阻塞会话（头阻塞程序）
查找导致阻塞的查询和事务（长时间保留锁的对象）
分析/理解长时间阻塞的原因
通过重新设计查询和事务解决阻塞问题

现在让我们深入讨论如何通过适当的数据捕获来确定主阻塞会话。

收集阻塞信息

为了降低对阻塞问题进行疑难解答的难度，数据库管理员可以使用 SQL 脚本持续监视 SQL Server 上的锁定和阻塞状态。要收集此数据，有两种免费方法。

第一种方法是查询动态管理对象 (DMO) 并存储结果，以便随着时间的推移进行比较。本文中引用的一些对象是动态管理视图 (DMV)，一些是动态管理函数 (DMF)。

第二种是使用扩展事件 (XEvents) 或 SQL 探查器跟踪来捕获正在执行的内容。由于 SQL 跟踪和 SQL Server 探查器已弃用，因此本疑难解答指南将重点介绍 XEvents。

从 DMV 收集信息

引用 DMV 来对阻塞进行故障排除的目的是识别阻塞链和 SQL 语句头的 SPID（会话 ID）。查找正在被阻塞的牺牲品 SPID。如果任何 SPID 被另一个 SPID 阻塞，则调查拥有资源的 SPID（阻塞的 SPID）。所有者 SPID 是否也被阻塞？你可以遍历链来找到头阻塞程序，然后调查其锁定的原因。

为此，可以使用下列方法之一：

在 SQL Server Management Studio (SSMS) “对象资源管理器”中，右键单击顶级服务器对象，依次展开“报表”、“标准报表”，然后选择“活动 - 所有阻塞事务”。此报表显示阻塞链头的当前事务。如果展开事务，报表将显示被头部事务阻止的事务。此报表还将显示“阻塞中的 SQL 语句”和“已阻塞的 SQL 语句”。
在 SSMS 中打开“活动监视器”，并引用“发起阻止方”列。在此处查找有关活动监视器的详细信息。

通过 DMV 还可获取更详细的基于查询的方法：

sp_who 命令和 sp_who2 命令是显示所有当前会话的较旧命令。 DMV sys.dm_exec_sessions 返回结果集中更易于查询和筛选的更多数据。需要在其他查询的核心查找 sys.dm_exec_sessions。
如果已经标识了特定会话，可以使用 DBCC INPUTBUFFER(<session_id>) 查找会话提交的最后一条语句。可以通过 sys.dm_exec_input_buffer 动态管理函数 (DMF) 返回类似的结果，该结果集提供 session_id 和 request_id，更易于查询和筛选。例如，若要返回由 session_id 66 和 request_id 0 提交的最新查询：

SELECT * FROM sys.dm_exec_input_buffer (66,0);

请参阅 sys.dm_exec_requests 并引用 blocking_session_id 列。当 blocking_session_id = 0 时，将不会阻塞会话。虽然 sys.dm_exec_requests 只列出当前正在执行的请求，但任何连接（活动或不活动）都将列在 sys.dm_exec_sessions 中。在下一个查询中，在 sys.dm_exec_requests 和 sys.dm_exec_sessions 之间建立此公共联接。请记住，要通过 sys.dm_exec_requests 返回，查询必须使用 SQL Server 主动执行。
使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV 运行此示例查询以查找活动执行的查询及其当前 SQL 批处理文本或输入缓冲区文本。如果 sys.dm_exec_sql_text 的 text 列返回的数据为 NULL，则当前未执行查询。在这种情况下，sys.dm_exec_input_buffer 的 event_info 列将包含传递给 SQL 引擎的最后一个命令字符串。此查询还可以用于标识阻止其他会话的会话，包括每个 session_id 阻止的 session_id 列表。

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

运行 Microsoft 支持部门提供的更详细的示例查询，以确定多个会话阻塞链头，包括阻塞链中涉及的会话的查询文本。

WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

若要捕获长时间运行或未提交的事务，请使用另一组 DMV 来查看当前打开的事务，包括 sys.dm_tran_database_transactions、 sys.dm_tran_session_transactions、 sys.dm_exec_connections 和 sys.dm_exec_sql_text 存在多个与跟踪事务相关联的 DMV，请在此处参阅与事务有关的 DMV。

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];

引用 sys.dm_os_waiting_tasks 位于 SQL Server 线程/任务层。这会返回有关请求当前遇到的 SQL wait_type 的信息。与 sys.dm_exec_requests 一样，sys.dm_os_waiting_tasks 只返回活动请求。

注意

有关等待类型的详细信息（包括随时间变化的聚合的等待统计信息），请参阅 DMV sys.dm_db_wait_stats。

使用 sys.dm_tran_locks DMV，获取有关查询所放置的锁的更详尽信息。此 DMV 可以返回产品 SQL Server 实例上的大量数据，并且可用于诊断当前保存的锁定。

由于 sys.dm_os_waiting_tasks 的内联，以下查询将 sys.dm_tran_locks 的输出限制为仅当前被阻塞的请求、其等待状态以及其锁：

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';

对于 DMV，随着时间的推移存储查询结果将提供数据点，允许你在指定的时间间隔内查看阻塞，以识别持久的阻塞或趋势。 CSS 排查此类问题的导航工具是使用 PSSDiag 数据收集器。此工具使用“SQL Server Perf 统计信息”随时间从上面引用的 DMV 收集结果集。由于此工具不断发展，请查看 GitHub 上 DiagManager 的最新公共版本。

从扩展事件收集信息

除了上述信息，通常还需要捕获服务器上活动的跟踪，以彻底调查 SQL Server 中的阻塞问题。例如，如果一个会话在一个事务中执行多条语句，则只表示提交的最后一条语句。但是，前面的语句之一可能是仍保留锁的原因。跟踪将使你能够查看当前事务中会话执行的所有命令。

可通过两种方法在 SQL Server 中捕获跟踪：扩展事件 (XEvents)和 Profiler 跟踪。但是，使用 SQL Server Profiler 的 SQL跟踪已弃用。 XEvents 是一种更新的、卓越的跟踪平台，它的功能性更强，对观测系统的影响更小，并且其界面已集成到 SSMS 中。

预先制作的扩展事件会话已准备好在 SSMS 启动，在 XEvent Profiler 菜单下的对象资源管理器中列出。要了解更多详细信息，请参阅 XEvent Profiler。还可以在 SSMS 中创建自己的自定义扩展事件会话，请参阅扩展事件新会话向导。若要排查阻塞问题，我们通常会捕获：

类别错误：
- Attention
- Blocked_process_report**
- Error_reported（通道管理员）
- Exchange_spill
- Execution_warning

**若要配置生成阻塞进程报告的阈值和频率，请使用 sp_configure 命令配置阻塞进程阈值选项，该选项可在几秒内设置完成。默认情况下，不生成阻塞的进程报告。

分类警告：
- Hash_warning
- Missing_column_statistics
- Missing_join_predicate
- Sort_warning
分类执行：
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
类别锁定
- Lock_deadlock
类别会话
- Existing_connection
- 登录
- Logout

发现并解决常见阻塞情况

通过检查上述信息，可以确定大多数阻塞问题的原因。本文的其余部分将讨论如何使用这些信息来识别和解决一些常见的阻塞情况。本讨论假定已使用阻塞脚本（此前已引用）来捕获有关阻塞 SPID 的信息，并已使用 XEvent 会话捕获应用程序活动。

分析阻塞数据

使用 blocking_these 和 session_id 检查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的输出，以确定阻塞链头。此操作可清楚地标识出哪些请求已被阻塞，哪些请求即将被阻塞。进一步了解已被阻塞和即将被阻塞的会话。阻塞链是否有公共点或根？它们可能共享一个公共表，并且阻塞链中涉及的一个或多个会话正在执行写入操作。

检查 DMV sys.dm_exec_requests 和 sys.dm_exec_sessions 的输出，获取有关阻塞链头上的 SPID 的信息。查找以下列：

sys.dm_exec_requests.status

此列显示特定请求的状态。通常， 睡眠状态表示 SPID 已完成执行，正在等待应用程序提交另一个查询或批处理。 “可运行”或“正在运行”状态表示 SPID 当前正在处理查询。下表简要说明了各种状态值。

状态	含义
背景	SPID 正在运行一个后台任务，例如死锁检测、日志编写器或检查点。
Sleeping	SPID 当前未执行。这通常表示 SPID 正在等待来自应用程序的命令。
运行	SPID 当前正在计划程序中运行。
可运行	SPID 在计划程序的可运行队列中等待获取计划程序时间。
已挂起	SPID 正在等待某个资源，如锁或闩锁。

sys.dm_exec_sessions.open_transaction_count

此列告知此会话中的打开的事务数。如果该值大于 0，则 SPID 位于打开的事务中，并且可能保留事务中任何语句获取的锁。打开的事务可能是由当前活动语句创建的，也可以是由过去运行的语句请求创建的，并且不再处于活动状态。
sys.dm_exec_requests.open_transaction_count

同样，此列告知此请求中打开的事务数。如果此值大于 0，SPID 位于打开的事务中，并且可能持有事务中任何活动语句获取的锁。与没有活动请求不同 sys.dm_exec_sessions.open_transaction_count，此列将显示 0。
sys.dm_exec_requests.wait_type、wait_time 和 last_wait_type

如果 sys.dm_exec_requests.wait_type 为 NULL，则请求当前没有等待任何内容，last_wait_type 值指示请求遇到的最后一个 wait_type。有关 sys.dm_os_wait_stats 的详细信息和最常见等待类型的说明，请参阅 sys.dm_os_wait_stats。 wait_time 值可用于确定请求是否正在进行。当针对 sys.dm_exec_requests 表的查询在 wait_time 列中返回的值小于前一个 sys.dm_exec_requests 查询中的 wait_time 值时，这表示已获取并释放前一个锁，现在正在等待一个新锁（假设为非零 wait_time）。这可以通过比较 sys.dm_exec_requests 输出之间的 wait_resource 来验证，该输出显示了请求正在等待的资源。

sys.dm_exec_requests.wait_resource

此列指示阻止的请求正在等待的资源。下表列出了常见的 wait_resource 格式及其含义：

资源	格式	示例	说明
表	DatabaseID:ObjectID:IndexID	TAB:5:261575970:1	在此案例中，数据库 ID 5 是 pubs 示例数据库， `object_id` 261575970 是标题表，而 1 是聚集索引。
页面	DatabaseID:FileID:PageID	页：5:1:104	在此例中，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，页 104 是属于标题表的页。若要标识该页所属的对象，请使用动态管理函数 sys.dm_db_page_info，从 `wait_resource` 传入 DatabaseID、FileId、PageId。
键	DatabaseID:Hobt_id（索引键的哈希值）	键：5:72057594044284928 (3300a4f361aa)	在此例中，数据库 ID 5 是 Pubs，Hobt_ID 72057594044284928 对应于 object_id 261575970（标题表）的 index_id 2。使用 `sys.partitions` 目录视图将 `hobt_id` 关联到特定 `index_id` 和 `object_id`。无法将索引键散列哈希解哈希为特定的键值。
行	DatabaseID:FileID:PageID:Slot(row)	RID：5:1:104:3	在此例中，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，页 104 是属于标题表的页，且槽 3 指示行在页上的位置。
Compile	DatabaseID:FileID:PageID:Slot(row)	RID：5:1:104:3	在此例中，数据库 ID 5 是 pubs，文件 ID 1 是主数据文件，页 104 是属于标题表的页，且槽 3 指示行在页上的位置。

sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV 包含有关打开事务的数据，这些事务可以联接到其他 DMV 以获得等待提交或回滚的事务的完整图。使用以下查询返回有关打开事务的信息，这些事务与其他 DMV 联接，包括 sys.dm_tran_session_transactions。请考虑事务的当前状态 (transaction_begin_time) 和其他情况数据，以评估它是否可能是阻塞源。

SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

其他列

页可以通过 sys.dm_exec_sessions 和 sys.dm_exec_request 中的其余列来了解问题的根源。它们的用处因问题的具体情况而异。例如，可以确定问题是否仅发生在某些客户端 (hostname)、某些网络库 (client_interface_name)、SPID 提交的最后批处理何时在 sys.dm_exec_sessions 中被 last_request_start_time、请求通过 sys.dm_exec_requests 中的 start_time 的运行时间等。

常见的阻塞情况

下表列出了常见症状及其可能原因。

wait_type、open_transaction_count 和 status 列是指由 sys.dm_exec_request 返回的信息，而其他列可能由 sys.dm_exec_sessions 返回。 “是否解决？”列指示阻塞是否将自行解决，或者是否应通过 KILL 命令终止会话。有关详细信息，请参阅 KILL (Transact-SQL)。

场景	Wait_type	Open_Tran	状态	Resolves?	其他症状
1	NOT NULL	>= 0	可运行	是，当查询完成时。	在 `sys.dm_exec_sessions` 中，`reads`、`cpu_time` 和/或 `memory_usage` 列将随着时间的推移而增加。完成后，查询的持续时间将较长。
2	Null	>0	正在睡眠	不可以，但可以终止 SPID。	此 SPID 的“扩展事件”会话中可能会显示注意信号，指示查询超时或已取消。
3	Null	>= 0	可运行	否。在客户端获取所有行或关闭连接之前无法解析。可以终止 SPID，但可能最多需要 30 秒。	如果 open_transaction_count = 0，并且 SPID 在事务隔离级别为默认值 (READ COMMITTED) 时保持锁定，则这可能是一个原因。
4	多种多样	>= 0	可运行	否。在客户端取消查询或关闭连接之前无法解析。可以终止 SPID，但可能最多需要 30 秒。	`sys.dm_exec_sessions` 中位于阻塞链头的 SPID 的 `hostname` 列将与它所阻塞的 SPID 相同。
5	Null	>0	回滚	是的。	此 SPID 的扩展事件会话中可能会显示注意信号，表明发生了查询超时或取消，或者只是发布了回滚语句。
6	Null	>0	正在睡眠	最终，当 Windows NT 确定会话不再活动时，连接将断开。	`sys.dm_exec_sessions` 中的 `last_request_start_time` 值比当前时间早得多。

详细的阻塞场景

方案 1：由正常运行的查询导致的阻塞，执行时间较长

在此方案中，主动运行的查询已获取锁，并且未释放锁（受事务隔离级别的影响）。因此，其他会话将等待锁，直到它们被释放。

解决方法：

解决此类阻塞问题的方法是寻找优化查询的方法。此类阻塞问题可能是性能问题，需要你根据这种情况进行操作。有关对特定慢速运行的查询进行故障排除的信息，请参阅如何对 SQL Server 上慢速运行的查询进行故障排除。有关详细信息，请参阅监视和优化性能。

来自“查询存储”的 SSMS 内置报表（在 SQL Server 2016 中引入）也是一种强烈推荐且宝贵的工具，可用于识别代价最高昂的查询和不理想的执行计划。

如果有一个长期运行的查询阻止其他用户且无法对其进行优化，请考虑将其从 OLTP 环境移动到专用报告系统。还可以使用 Always On 可用性组同步“数据库的只读副本”。

注意

查询执行期间的阻塞可能是由查询升级引起的，这种情况下行锁定或页锁定升级到表锁定。 Microsoft SQL Server 会动态确定何时执行锁升级。防止锁升级的最简单和最安全的方法是保持事务简短，并减少昂贵查询的锁占用量，以免超过锁升级阈值。有关检测和防止过度锁升级的详细信息，请参阅解决锁升级导致的阻塞问题。

方案 2：由具有未提交的事务的休眠 SPID 导致的阻塞

这种类型的阻塞通常可以通过正在休眠或等待事务嵌套级别（@@TRANCOUNT，来自 sys.dm_exec_requests 的 open_transaction_count）大于零的命令的 SPID 来识别。如果应用程序遇到查询超时，或者发出取消语句的同时没有发出所需的 ROLLBACK 和/或 COMMIT 语句数，则可能会发生这种情况。当 SPID 收到查询超时或取消时，它将终止当前查询和批处理，但不会自动回滚或提交事务。应用程序对此负有责任，因为 SQL Server 不能假定由于单个查询被取消而必须回滚整个事务。查询超时或取消将在扩展事件会话中显示为 SPID 的注意信号事件。

若要演示未提交的显式事务，请发出以下查询：

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

然后，在同一窗口中执行以下查询：

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

第二个查询的输出结果表明，事务计数为 1。在提交或回滚事务之前，仍会持有在事务中获取的所有锁。如果应用程序显式打开并提交事务，则通信或其他错误可能会使会话及其事务处于打开状态。

使用本文前面部分的脚本，根据 sys.dm_tran_active_transactions 来标识实例中当前未提交的事务。

解决方法：

此外，此类阻塞问题也可能是性能问题，需要你执行这样的操作。如果可以缩短查询执行时间，则不会出现查询超时或取消的情况。应用程序要能够处理超时或取消的情况（如果出现），这点很重要，但你也可以从检查查询的性能中获益。
应用程序必须正确管理事务嵌套级别，否则在以这种方式取消查询之后，它们可能会导致阻塞问题。考虑以下情况：
- 在客户端应用程序的错误处理程序中，在出现任何错误后执行 IF @@TRANCOUNT > 0 ROLLBACK TRAN，即使客户端应用程序不认为某个事务已打开。需要检查打开的事务，因为批处理期间调用的存储过程可能在客户端应用程序不知情的情况下启动事务。某些条件（如取消查询）会阻止过程在当前语句之后执行，因此即使过程具有检查 IF @@ERROR <> 0 和中止事务的逻辑，在这种情况下也不会执行此回滚代码。
- 如果在打开连接的应用程序中使用连接池，并在将连接释放回池之前运行一些查询（例如基于 Web 的应用程序），则暂时禁用连接池可能有助于缓解问题，直到修改客户端应用程序以适当地处理错误。通过禁用连接池，释放连接将导致物理断开 SQL Server 连接，从而导致服务器回滚所有打开的事务。
- 将 SET XACT_ABORT ON 用于连接，或用于在任何开始事务且未在错误后进行清理的存储过程。如果发生运行时错误，此设置将中止任何打开的事务并将控制权返回给客户端。有关详细信息，请查看 SET XACT_ABORT (Transact-SQL)。

注意

连接在从连接池重复使用之前不会重置，因此用户可以打开事务，然后释放与连接池的连接，但在几秒之内可能不会重复使用，在此期间事务将保持打开状态。如果未重复使用连接，则当连接超时并从连接池中删除时，事务将中止。因此，客户端应用程序最好中止其错误处理程序中的事务，或使用 SET XACT_ABORT ON 来避免这种潜在的延迟。

注意

在 SET XACT_ABORT ON 之后，不会执行导致错误的语句之后的 T-SQL 语句。这可能会影响现有代码的预期流。

方案 3：由 SPID 导致的阻塞，其相应的客户端应用程序未提取所有结果行直至完成

向服务器发送查询后，所有应用程序必须立即将所有结果行提取到完成。如果应用程序没有提取所有结果行，则表上可能会留下锁，从而阻塞其他用户。如果使用的应用程序透明地向服务器提交 SQL 语句，则应用程序必须提取所有结果行。如果没有提取所有结果行（并且无法将其配置为执行此操作），则可能无法解决阻塞问题。若要避免此问题，你可以将性能不佳的应用程序限制在报表或决策支持数据库中，与主 OLTP 数据库分离。

解决方法：

必须重写应用程序才能提取所有结果行直至完成。这并不排除使用查询的 ORDER BY 子句中的 OFFSET 和 FETCH 来执行服务器端分页。

方案 4：由分布式客户端/服务器死锁导致的阻塞

与传统的死锁不同，使用 RDBMS 锁定管理器无法检测分布式死锁。这是因为死锁中涉及的资源只有一个是 SQL Server 锁。死锁的另一端处于客户端应用程序级别，SQL Server 对此没有控制权。下面两个示例说明如何执行此操作，以及应用程序可以避免这种情况的可能方法。

示例 A：具有单一客户端线程的客户端/服务器分布式死锁

如果客户端有多个打开的连接和单个执行线程，则可能会出现以下分布式死锁。注意，此处使用的术语 dbproc 指的是客户端连接结构。

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

在上面所示案例中，单一客户端应用程序线程有两个打开的连接。它在 dbproc1 上异步提交 SQL 操作。这意味着它不会等待调用返回，然后再继续。然后，应用程序在 dbproc2 上提交另一个 SQL 操作，并等待结果以开始处理返回的数据。当数据开始返回（以最先响应的 dbproc 为准--假定是 dbproc1），它将处理完在该 dbproc 上返回的所有数据。它从 dbproc1 提取结果，直到 SPID1 被 SPID2 持有的锁阻塞（因为这两个查询在服务器上异步运行）。此时，dbproc1 将无限期地等待更多数据。不会在锁上阻止 SPID2，而是尝试将数据发送到其客户端 dbproc2。但是，dbproc2 在应用程序层的 dbproc1 上被有效阻止，因为 dbproc1 正在使用应用程序的单个执行线程。这会导致死锁，同时 SQL Server 无法检测或解析，因为所涉及的资源中只有一个是 SQL Server 资源。

示例 B：客户端/服务器分布式死锁（每个连接都有一个线程）

即使客户端上每个连接都存在单独的线程，此分布式死锁的变体也可能仍会出现，如下所示。

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

这种情况类似于示例 A，不同的是 dbproc2 和 SPID2 正在运行 SELECT 语句，以便执行行处理（一次一行），并通过缓冲区将每行传递给 dbproc1，以获取同一表上的 INSERT、UPDATE 或 DELETE 语句。最终，SPID1（执行 INSERT、UPDATE 或 DELETE）被 SPID2 持有的锁阻塞（执行 SELECT）。 SPID2 将结果行写入客户端 dbproc2。然后，Dbproc2 尝试将缓冲区中的行传递到 dbproc1，但发现 dbproc1 正忙（它在等待 SPID1 完成当前在 SPID2 上被阻塞的 INSERT 时被阻塞）。此时，dbproc2 在应用程序层被 dbproc1 阻塞，而 dbproc1 的 SPID (SPID1) 在数据库级别被 SPID2 阻塞。同样，这会导致死锁，同时 SQL Server 无法检测或解析，因为所涉及资源中只有一个是 SQL Server 资源。

A 和 B 这两个示例都是应用程序开发人员必须注意的基本问题。他们必须对应用程序进行代码编码，以便正确处理这些情况。

解决方法：

提供查询超时后，如果发生分布式死锁，则在超时时会中断。有关使用查询超时的详细信息，请参阅连接提供程序文档。

方案 5：由处于回滚状态的会话导致的阻塞

在用户定义的事务之外终止或取消的数据修改查询将回滚。这也可能是客户端网络会话断开连接的副作用，或者请求被选为死锁牺牲品。这通常可以通过观察 sys.dm_exec_requests 的输出来识别，它可能指示回滚 command，并且 percent_complete 列可能会显示进度。

在用户定义的事务之外终止或取消的数据修改查询将回滚。出现这种情况也可能是客户端计算机重启及其网络会话断开连接的副作用。同样，被选为死锁受害者的查询也会回滚。数据修改查询的回滚速度通常不能比最初应用更改的速度更快。例如，如果 DELETE、INSERT 或 UPDATE 语句已运行一小时，则至少可能需要一个小时才能回滚。这是预期的行为，因为必须回滚所做的更改，否则数据库中的事务和物理完整性将受到损害。由于必须执行此操作，SQL Server 将 SPID 标记为黄金或回滚状态（这意味着它不能被终止或选为死锁受害者）。这通常可以通过观察 sp_who 的输出来识别，这可能指示 ROLLBACK 命令。 sys.dm_exec_sessions 的 status 将指示 ROLLBACK 状态。

注意

启用“加速数据库恢复功能”时，很少出现冗长回滚。此功能已在 SQL Server 2019 中推出。

解决方法：

必须等待会话完成回滚所做的更改。

如果实例在此操作过程中关闭，则数据库在重启时将处于恢复模式，并且在处理所有打开的事务之前将无法访问它。启动恢复每个事务所用的时间与运行时恢复的时间基本相同，在此期间数据库不可访问。因此，强制服务器关闭以修复处于回滚状态的 SPID 通常会适得其反。在启用了“加速数据库恢复”的 2019 SQL Server 中，不应发生这种情况。

若要避免这种情况，请勿在 OLTP 系统的繁忙时间执行大批量写入操作或索引创建或维护操作。如果可能，请在低活动期间执行此类操作。

方案 6：孤立事务导致的阻塞

这是一个常见的问题方案，部分与方案 2重叠。如果客户端应用程序停止、客户端工作站重启或出现批量中止错误，这些都可能会使事务保持打开状态。如果应用程序未回滚应用程序 CATCH 或 FINALLY 阻止中的事务，或者未处理这种情况，则可能会出现这种情况。

在这种情况下，虽然已取消执行 SQL 批处理，但应用程序会保持 SQL 事务的打开状态。从 SQL Server 实例的角度来看，客户端似乎仍然存在，获取的任何锁仍会保留。

若要演示孤立事务，请执行以下查询，该查询通过将数据插入不存在的表来模拟批量中止错误：

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

然后，在同一窗口中执行以下查询：

SELECT @@TRANCOUNT;

第二个查询的输出结果表明，事务计数为 1。在提交或回滚事务之前，仍会持有在事务中获取的所有锁。由于该批处理已被查询中止，因此执行该批处理的应用程序可能会继续在同一会话上运行其他查询，而无需清理仍处于打开状态的事务。锁定将一直保留到会话终止或重启 SQL Server 实例。

解决方法：

防止这种情况的最佳方法是改进应用程序错误/异常处理方式，尤其是针对意外终止的情况。确保在应用程序代码中使用 Try-Catch-Finally 块，并在出现异常时回滚事务。
考虑将 SET XACT_ABORT ON 用于会话，或用于在任何开始事务且未在错误后进行清理的存储过程。如果出现中断批处理的运行时错误，此设置将自动回滚所有打开的事务，并将控制权返回给客户端。有关详细信息，请查看 SET XACT_ABORT (Transact-SQL)。
要解析已断开连接但未适当清理其资源的客户端应用程序的孤立连接，可以使用 KILL 命令终止 SPID。有关参考，请参阅 KILL (Transact-SQL)。

KILL 命令采用 SPID 值作为输入。例如，要终止 SPID 9，请运行以下命令：

KILL 99

注意

由于检查 KILL 命令存在时间间隔，因此，KILL 命令最多可能需要 30 秒才能完成。

通过

了解和解决 SQL Server 阻塞问题

目标

什么是阻塞

应用程序和阻塞

对阻塞进行故障排除

收集阻塞信息

从 DMV 收集信息

从扩展事件收集信息

发现并解决常见阻塞情况

分析阻塞数据

常见的阻塞情况

详细的阻塞场景

方案 1：由正常运行的查询导致的阻塞，执行时间较长

方案 2：由具有未提交的事务的休眠 SPID 导致的阻塞

方案 3：由 SPID 导致的阻塞，其相应的客户端应用程序未提取所有结果行直至完成

方案 4：由分布式客户端/服务器死锁导致的阻塞

示例 A：具有单一客户端线程的客户端/服务器分布式死锁

示例 B：客户端/服务器分布式死锁（每个连接都有一个线程）

方案 5：由处于回滚状态的会话导致的阻塞

方案 6：孤立事务导致的阻塞

另请参阅

反馈

其他资源