排查由编译锁导致的阻塞问题

2025-01-09

本文介绍如何排查和解决编译锁导致的阻塞问题。

原始产品版本：SQL Server
原始 KB 数： 263889

总结

在 Microsoft SQL Server 中，存储过程计划的一个副本通常一次在缓存中。强制实施这需要对编译过程的一些部分进行序列化，并且此同步是通过使用编译锁完成的。如果许多连接同时运行同一存储过程，并且每次运行时都必须为该存储过程获取编译锁，则会话 ID（SPID）可能会开始相互阻止，因为它们每次尝试获取对象的独占编译锁。

以下是可在阻塞输出中观察到的编译阻塞的一些典型特征：

waittype 对于被阻止和（通常）阻塞会话 SPID 是 LCK_M_X （独占）， waitresource 并且是窗体 OBJECT: dbid: object_id [[COMPILE]]，其中 object_id 是存储过程的对象 ID。
阻止程序具有 waittype NULL 状态可运行。被阻止的会话具有 waittype LCK_M_X （独占锁），状态处于睡眠状态。
尽管阻塞事件的总体持续时间可能很长，但没有单个会话（SPID）长时间阻止其他 SPID。有滚动阻塞;一旦一个编译完成，另一个 SPID 就会接管头部阻塞器的角色，几秒钟或更少，依此推。

以下信息来自此类阻塞期间快照 sys.dm_exec_requests ：

session_id   blocking_session_id   wait_type   wait_time   waitresource 
----------   -------------------   ---------   ---------   ----------------------------
221           29                   LCK_M_X     2141        OBJECT: 6:834102 [[COMPILE]]
228           29                   LCK_M_X     2235        OBJECT: 6:834102 [[COMPILE]]
29            214                  LCK_M_X     3937        OBJECT: 6:834102 [[COMPILE]]
13            214                  LCK_M_X     1094        OBJECT: 6:834102 [[COMPILE]]
68            214                  LCK_M_X     1968        OBJECT: 6:834102 [[COMPILE]]
214           0                    LCK_M_X     0           OBJECT: 6:834102 [[COMPILE]]

waitresource在列中（6：834102），6 是数据库 ID，834102是对象 ID。此对象 ID 属于存储过程，而不是表。

导致编译锁的方案

以下方案描述了存储过程或触发器上独占编译锁的原因。

存储过程在没有完全限定名称的情况下执行

运行存储过程的用户不是过程的所有者。
存储过程名称未使用对象所有者的名称完全限定。

例如，如果用户 dbo 拥有对象 dbo.mystoredproc 和另一个用户， Harry则通过使用命令 exec mystoredproc运行此存储过程，则对象名称的初始缓存查找将失败，因为对象不是所有者限定的。（目前还不清楚是否存在另一个名为 Harry.mystoredproc 的存储过程。因此，SQL Server 无法确定缓存的计划是正确的执行计划 dbo.mystoredproc 。然后，SQL Server 获取过程的独占编译锁，并准备编译过程。这包括将对象名称解析为对象 ID。在 SQL Server 编译计划之前，SQL Server 使用此对象 ID 对过程缓存执行更精确的搜索，并且即使没有所有者资格，也可以找到以前编译的计划。

如果找到现有计划，SQL Server 将重复使用缓存的计划，并且实际上不会编译存储过程。但是，缺少所有者资格会强制 SQL Server 执行第二个缓存查找，并在程序确定现有缓存执行计划可以重复使用之前获取独占编译锁。获取锁并执行查找以及到达此点所需的其他工作可能会给导致阻塞的编译锁带来延迟。如果许多不是存储过程所有者的用户同时运行该过程而不提供所有者名称，则尤其如此。即使看不到等待编译锁的 SPID，缺少所有者资格可能会导致存储过程执行延迟并导致 CPU 使用率过高。

发生此问题时，SQL Server 扩展事件会话中记录了以下事件序列。

事件名称	文本
rpc_starting	mystoredproc
sp_cache_miss	mystoredproc
sql_batch_starting	mystoredproc
sp_cache_hit	mystoredproc
...	...

sp_cache_miss 当按名称查找缓存失败时发生，但在将不明确的对象名称解析为对象 ID 且存在 sp_cache_hit 事件后，最终在缓存中找到匹配的缓存计划。

编译锁定问题的解决方法是确保对存储过程的引用是所有者限定的。（而不是 exec mystoredproc，请使用 exec dbo.mystoredproc。）由于性能原因，所有者资格非常重要，但不必使用数据库名称限定存储的代理，以防止额外的缓存查找。

使用标准阻止故障排除方法可以检测由编译锁引起的阻塞。

存储过程经常重新编译

重新编译是存储过程或触发器上编译锁的一个解释。导致存储过程重新编译的方法包括 EXECUTE... WITH RECOMPILE、 CREATE PROCEDURE ...WITH RECOMPILE或使用 sp_recompile。有关详细信息，请参阅重新编译存储过程。在这种情况下，解决方案是减少或消除重新编译。

存储过程的前缀为 sp_**

如果存储过程名称以 sp_ 前缀开头，并且它不在 master 数据库中，则即使所有者限定存储过程，也会在每次执行的缓存命中之前看到 sp_cache_miss 。这是因为 sp_ 前缀告知 SQL Server 存储过程是系统存储过程，并且系统存储过程具有不同的名称解析规则。（首选位置位于 master 数据库中。用户创建的存储过程的名称不应以 sp_.

使用不同的大小写（大/下）调用存储过程

如果使用与用于创建它的情况不同的字母大小写（大写或更低）运行所有者限定的过程，则该过程可以触发 CacheMiss 事件或请求 COMPILE 锁。为了说明，请注意在与EXEC dbo.salesdata以前CREATE PROCEDURE dbo.SalesData ...使用的不同字母大小写。最终，该过程使用缓存的计划，并且不会重新编译。但是，对 COMPILE 锁的请求有时可能会导致 前面所述的阻塞链 情况。如果有许多会话（SPID）尝试使用与用于创建它的情况不同的事例来执行相同的过程，则可能会发生阻塞链。无论在服务器或数据库上使用的排序顺序或排序规则如何，都是如此。此行为的原因是，用于查找缓存中的过程的算法基于哈希值（对于性能），如果大小写不同，哈希值可能会更改。

解决方案是使用与应用程序执行过程时使用的字母大小写相同的字母大小写来删除和创建过程。还可以使用正确的大小写（大写或小写）确保所有应用程序中执行该过程。

存储过程作为语言事件调用

如果尝试将存储过程作为语言事件而不是 RPC 执行，SQL Server 必须分析和编译语言事件查询，确定查询正在尝试执行特定过程，然后尝试在缓存中查找该过程的计划。若要避免 SQL Server 必须分析并编译语言事件的情况，请确保将查询作为 RPC 发送到 SQL Server。例如，在 .NET 代码中，可用于 SqlCommand.CommandType.StoredProcedure 确保 RPC 事件。

存储过程或sp_executesql使用大于 8 KB 的字符串参数

如果调用存储过程或 sp_executesql 并传递大于 8 KB 的字符串参数，SQL Server 将使用二进制大型对象（BLOB）数据类型来存储参数。因此，此执行的查询计划不会保留在计划缓存中。因此，存储过程的每个执行或 sp_executesql 必须获取编译锁才能编译新计划。执行完成后，将放弃此计划。有关详细信息，请参阅执行计划缓存中的说明，并重复使用大于 8 KB 的字符串文本。若要避免此方案中的编译锁，请将参数的大小减小到小于 8 KB。

参考

OPEN SYMMETRIC KEY 命令阻止查询计划缓存

通过