主页 / dba / 问题 / 167178
Accepted
Tara Kizer
Asked: 2017-03-15 10:54:56 +0800 CST

缺少存储过程的执行计划

  • 772

存储过程的缓存中缺少计划的原因是什么？

  1. WITH RECOMPILE
  2. 动态 SQL
  3. 加密代码
  4. 重大数据变化
  5. 更新统计
  6. 还有什么？

我最近在 2 台服务器（SQL Server 2008 R2 和 SQL Server 2012）上工作过，这些服务器没有针对资源密集型存储过程的缓存计划。存储过程中的许多（也许是全部）语句也没有缓存中的计划。一些存储过程非常频繁地执行，例如每秒几次。

没有任何记忆压力。其中一台服务器的硬件比需要的多得多。

我认为缺少计划是由于在存储过程中间创建了临时表，但这似乎是 SQL Server 2000 或更早版本的旧信息。从 SQL Server 2005 开始，重新编译发生在 DDL 之后的语句的语句级别。在所有情况下都是如此，还是在较新的版本上仍然会发生？

还有什么可能是缺少计划的罪魁祸首？我浏览了几篇关于这个主题的文章，但似乎没有什么合适的。

我本周正在查看的服务器上启用了针对临时工作负载的优化。其中一个存储过程每天只执行一次。我确实有那个的代码。我没有每分钟执行超过 100 次的代码，但我可以得到它。我将无法发布代码，但我可以根据我的问题对其进行描述。

我不相信有人在释放过程缓存或删除干净的缓冲区。该客户使用 Solarwinds DPA 作为其监控工具之一。DPA 确实捕获了每天调用一次的存储过程中的语句的执行计划之一。WHERE由于 non-sargable子句，该语句具有大量读取。如果 DPA 捕获了该语句，那么它是一个估计的计划，并且曾经在计划缓存中。只是在我们进行故障排除时不在那里。我会让他们开始记录sp_WhoIsActive到一张桌子上。

我正在使用sp_BlitzCache. （我为 Brent Ozar Unlimited 工作）这将显示整个存储过程的计划以及单个语句的计划（如果存在）。如果它们不存在，则会出现警告“我们找不到此查询的计划。可能的原因包括动态 SQL、RECOMPILE提示和加密代码。” 该警告也在声明中。

TF 2371 没有到位。我正在查看等待统计数据。服务器很无聊。PLE 超过 130,000。

我现在有另外 2 个存储过程的代码。其中之一是使用动态 SQL，exec (@sql)以便我们知道为什么没有针对它的计划。但是另一台，也就是每分钟跑100次以上的，没有任何异常。唯一突出的是临时表是在超过 1000 行代码的中间创建的。它也确实调用了一堆子存储过程。

关于SQL Server 2008 中的计划缓存，我没有看到任何文字 >= 8k，但其中一个存储过程在调用另一个存储过程之前对批量插入有注释。但是批量插入没有出现在我正在查看的外部存储过程中。文章的“重新编译阈值”部分很有趣。我看到的临时表是大量的插入（可能导致数百万行）、一些更新和删除。因此临时表的大量数据更改。百万。

sql-server plan-cache

2 个回答

  1. Best Answer

    有两个计划缓存 DMF：

    sys.dm_exec_query_plan - 返回 XML 格式的缓存计划，但最多只能达到一定的大小（并且只要它们可以在 SQL Server 中格式化为 XML，这意味着最多 128 个嵌套级别。）

    sys.dm_exec_text_query_plan - 返回任何大小的文本格式的缓存计划。但缺点是计划很大时，不能在 SQL Server 内部将它们转换为 XML，甚至 TRY_CONVERT as XML 也会返回 null。

    sp_BlitzCache 只命中以前的 DMV（因为它需要将查询计划分析为 XML 来进行各种切片和切块。）我在Github 问题 #838中进行了改进，因此我们至少可以提醒用户去检查他们的 sys.dm_exec_text_query_plan更大的查询。但是，我们仍然无法对其进行 XML 分析。

    • 6

  2. 可能您需要调整 SSMS 可以返回到网格的 XML 的最大大小？

    • 1

相关问题