主页 / dba / 问题 / 6502
Accepted
msgisme
Asked: 2011-10-05 07:54:26 +0800 CST

代码在临时运行时与在存储过程中创建不同的计划

  • 772

我有一个删除语句，在存储过程中运行时使用了错误的计划，但在临时运行时选择了更好的计划。

我已经为查询使用的表重建了所有索引并删除了所有缓存。优化器仍然为存储过程选择了错误的计划。

我想知道为什么优化器对存储过程和临时 SQL 使用不同的执行计划。

sql-server execution-plan

2 个回答

  1. Best Answer

    普通嫌犯：

    1. adhoc 中的常量，代码中的参数。
      然后优化器可以为常数选择最佳计划。
      改变常数=改变计划。参数化计划是可重用的。

    2. 代码中的数据类型不匹配。
      由于数据类型优先，将引入隐式转换。
      例如 varchar 列与 nvarchar 参数相比。

    3. 参数嗅探。
      使用参数掩码或 OPTIMIZE FOR UNKNOWN

    测试：运行存储过程，运行sp_updatestats，再次运行。这将使缓存的计划无效，这比清除整个计划缓存要好。

    您可以通过多种方式禁用嗅探。主要的3个是

    • RECOMPILE. 在我看来，这很愚蠢。
    • OPTIMIZE FOR UNKNOWN
    • 参数屏蔽

    参数屏蔽如下所示：

    DECLARE @MaskedParam varchar(10)
SELECT @MaskedParam = @SignaureParam

SELECT...WHERE column = @MaskedParam

    掩蔽和OPTIMIZE提示具有相同的效果（可能出于不同的原因）。也就是说，优化器必须使用统计数据和数据分布来根据自己的优点评估参数？ ，而不是他们最后一次打电话。优化器可以重新编译也可以不重新编译。SQL Server 2005 添加了语句级重新编译，因此影响较小。

    现在，为什么与屏蔽/“未知”参数相比，带有“嗅探”参数的计划是“粘性”的，我不确定。

    自 SQL Server 2000 以来，除了最简单的代码之外，我一直使用参数掩码。我已经注意到，更复杂的代码很可能会发生这种情况。在我以前的工作中，我有一些可以更改计划参数默认值的报告过程。我认为“货物崇拜”方法比支持电话更容易。

    • 据我所知，参数屏蔽并OPTIMISE FOR UNKNOWN具有相同的效果。
      该提示比屏蔽更清晰，但已在 SQL Server 2008 中添加。

    • 参数嗅探发生在编译时。
      WITH RECOMPILE每次执行都会生成一个新计划。这意味着选择不当的默认值会影响计划。在我的上一份工作中，我可以使用一些报告代码轻松地演示这一点：更改参数默认值会改变计划，而不管提供的参数如何。

    • 这篇 MS Connect 文章很有趣：存储过程中的次优索引使用（在下面的 SO 答案之一中提到）

    • Bob Beauchemin 也提到过

    显着的问题

    • 嗅探仍然适用WITH RECOMPILE吗？也就是说，如果优化器知道放弃该计划，它是否旨在重用？

    • 为什么嗅探的计划“粘性”？

    SO的链接：

    • 6

  2. 不要忘记您为连接计划设置的 ANSI 设置在执行计划选择中起作用。当应用程序调用存储过程时，它的 ANSI 设置可能与您的 SSMS 连接不同。

    • 3

相关问题