排查由编译锁引起的阻塞问题

  • 项目

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

原始产品版本：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，状态为可运行。 阻止者具有 waittypeLCK_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 属于存储过程，而不是表。

更多信息

存储过程重新编译是存储过程或触发器上的编译锁的一种解释。 在这种情况下，解决方案是减少或消除重新编译。

导致编译锁的其他方案

  1. 存储过程执行时没有完全限定名称

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

    例如，如果用户 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扩展事件会话中。

    事件名称 Text
    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.) 虽然所有者资格对于性能原因很重要，但不必使用数据库名称限定存储的处理，以防止额外的缓存查找。

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

  2. 存储过程的前缀为 sp_

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

  3. 使用大小写 (上/下) 调用存储过程

    如果使用 (大小写) 用于创建所有者的过程执行所有者限定过程，则该过程可以触发 CacheMiss 事件或请求编译锁。 最终，该过程使用缓存的计划，并且不会重新编译。 但是，如果有许多 SPID 尝试使用与创建锁时所用的大小写不同的大小写来执行相同的过程，则对编译锁的请求有时可能会导致 阻塞链 的情况。 无论在服务器或数据库上使用的排序顺序或排序规则如何，都是如此。 此行为的原因是，用于在缓存中查找过程的算法基于性能) (的哈希值，如果大小写不同，哈希值可能会更改。

    解决方法是使用与应用程序执行过程时所使用的相同大小写删除并创建过程。 还可以使用正确的大小写 () ，确保从所有应用程序执行该过程。

  4. 存储过程作为 Language 事件调用

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

    有关详细信息，请参阅联机丛书中的 “创建存储过程” 一文中的“系统 存储过程”部分。

References

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