主页 / dba / 问题 / 33698
Accepted
RThomas
Asked: 2013-01-29 14:29:53 +0800 CST

参数嗅探 vs 变量 vs 重新编译 vs 优化未知

  • 772

所以今天早上我们有一个长时间运行的过程导致问题（30 秒 + 运行时间）。我们决定检查参数嗅探是否是罪魁祸首。因此，我们重写了 proc 并将传入的参数设置为变量，以击败参数嗅探。一种尝试/真实的方法。Bam，查询时间得到改善（不到 1 秒）。在查看查询计划时，可以在原始索引未使用的索引中发现改进。

只是为了验证我们没有得到误报，我们对原始 proc 执行了 dbcc freeproccache 并重新运行以查看改进的结果是否相同。但是，令我们惊讶的是，原来的 proc 仍然运行缓慢。我们再次尝试了 WITH RECOMPILE，但仍然很慢（我们尝试在调用 proc 和 proc 内部重新编译）。我们甚至重新启动了服务器（显然是开发箱）。

所以，我的问题是......当我们在一个空的计划缓存上得到同样的慢查询时，参数嗅探怎么能怪罪......不应该有任何参数来嗅探？

相反，我们是否受到与计划缓存无关的表统计信息的影响。如果是这样，为什么将传入参数设置为变量会有所帮助？

在进一步的测试中，我们还发现在 proc 的内部插入 OPTION (OPTIMIZE FOR UNKNOWN) 确实得到了预期的改进计划。

所以，你们中的一些人比我聪明，你能提供一些线索来说明幕后发生的事情会产生这种结果吗？

另一方面，慢速计划也有理由提前中止，GoodEnoughPlanFound而快速计划在实际计划中没有提前中止原因。

总之

  • 根据传入参数创建变量（1 秒）
  • 重新编译（30+秒）
  • dbcc freeproccache (30+ 秒)
  • 选项（针对 UKNOWN 进行优化）（1 秒）

更新：

在此处查看慢速执行计划： https ://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

在此处查看快速执行计划： https ://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

注意：出于安全原因，表、模式、对象名称已更改。

sql-server-2008 execution-plan

3 个回答

  1. Best Answer

    查询是

    SELECT SUM(Amount) AS SummaryTotal
FROM   PDetail WITH(NOLOCK)
WHERE  ClientID = @merchid
       AND PostedDate BETWEEN @datebegin AND @dateend

    该表包含 103,129,000 行。

    快速计划通过 ClientId 使用日期的剩余谓词进行查找，但需要进行 96 次查找才能检索Amount. 计划中的<ParameterList>部分如下。

            <ParameterList>
          <ColumnReference Column="@dateend" 
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@datebegin" 
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@merchid" 
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

    慢速计划按日期查找并查找以评估 ClientId 上的剩余谓词并检索数量（估计 1 与实际 7,388,383）。该<ParameterList>部分是

            <ParameterList>
          <ColumnReference Column="@EndDate" 
                           ParameterCompiledValue="'2013-02-01 23:59:00.000'" 
                           ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
          <ColumnReference Column="@BeginDate" 
                           ParameterCompiledValue="'2013-01-01 00:00:00.000'"               
                           ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
          <ColumnReference Column="@ClientID" 
                           ParameterCompiledValue="(78155)" 
                           ParameterRuntimeValue="(78155)" />
        </ParameterList>

    在第二种情况下，ParameterCompiledValue不为空。SQL Server 成功嗅探查询中使用的值。

    《SQL Server 2005 Practical Troubleshooting》一书有关于使用局部变量的说法

    使用局部变量来阻止参数嗅探是一个相当常见的技巧，但是OPTION (RECOMPILE)和OPTION (OPTIMIZE FOR)提示 ... 通常是更优雅且风险较小的解决方案

    ＃＃＃笔记

    在 SQL Server 2005 中，语句级编译允许将存储过程中的单个语句的编译推迟到第一次执行查询之前。到那时，局部变量的值将是已知的。从理论上讲，SQL Server 可以利用它来嗅探局部变量值，就像它嗅探参数一样。但是，由于在 SQL Server 7.0 和 SQL Server 2000+ 中通常使用局部变量来阻止参数嗅探，因此 SQL Server 2005 中未启用对局部变量的嗅探。它可能会在未来的 SQL Server 版本中启用，但这是一个很好的选择如果您可以选择，有理由使用本章中概述的其他选项之一。

    从一个快速测试来看，上述行为在 2008 年和 2012 年仍然相同，并且变量不会被嗅探以进行延迟编译，但只有在使用显式OPTION RECOMPILE提示时才会被嗅探。

    DECLARE @N INT = 0

CREATE TABLE #T ( I INT );

/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM   master..spt_values
WHERE  number = @N
       AND EXISTS(SELECT COUNT(*) FROM #T)

SELECT *
FROM   master..spt_values
WHERE  number = @N
OPTION (RECOMPILE)

DROP TABLE #T

    尽管延迟编译，变量没有被嗅探并且估计的行数不准确

    估计与实际

    所以我假设慢速计划与查询的参数化版本有关。

    ParameterCompiledValue所有参数都等于，所以这ParameterRuntimeValue不是典型的参数嗅探（计划是针对一组值编译的，然后针对另一组值运行）。

    问题是为正确的参数值编译的计划是不合适的。

    您可能会遇到此处和此处描述的升序日期的问题。对于具有 1 亿行的表，您需要插入（或以其他方式修改）2000 万行，SQL Server 才会自动为您更新统计信息。似乎上次更新它们时，零行与查询中的日期范围相匹配，但现在有 700 万行。

    您可以安排更频繁的统计更新，考虑跟踪标志2389 - 90或使用OPTIMIZE FOR UKNOWN它只是依靠猜测，而不是能够使用datetime列上当前具有误导性的统计信息。

    这在 SQL Server 的下一版本（2012 年之后）中可能不需要。相关的Connect 项目包含有趣的响应

    Microsoft 于 2012 年 8 月 28 日下午 1:35 发布
    我们已经为下一个主要版本进行了基数估计增强，基本上解决了这个问题。一旦我们的预览出来，请继续关注细节。埃里克

    Benjamin Nevarez 在文章结尾处研究了 2014 年的改进：

    初步了解新的 SQL Server 基数估计器。

    在这种情况下，新的基数估计器似乎会回退并使用平均密度，而不是给出 1 行估计。

    有关 2014 基数估计器和升序关键问题的一些额外细节：

    SQL Server 2014 中的新功能 - 第 2 部分 - 新基数估计

    • 44

  2. 所以，我的问题是……当我们在一个空的计划缓存上得到同样的慢查询时，参数嗅探怎么能怪罪……不应该有任何参数可以嗅探？

    当 SQL Server 编译包含参数值的查询时，它会嗅探这些参数的特定值以进行基数（行数）估计。在您的情况下，在选择执行计划时使用@BeginDate和@EndDate的特定值。您可以在此处和此处@ClientID找到有关参数嗅探的更多详细信息。我提供这些背景链接是因为上面的问题让我认为这个概念目前还没有完全理解——编译计划时总是有参数值需要嗅探。

    无论如何，这都是无关紧要的，因为参数嗅探不是 Martin Smith 指出的问题。在编译慢查询时，统计数据表明没有嗅探到的值的行@BeginDate和@EndDate：

    缓慢的计划嗅到了价值观

    嗅出的值是最近的，表明Martin 提到的上升的关键问题。因为估计日期上的索引查找仅返回一行，所以优化器选择一个计划，将谓词ClientID作为残差推送到 Key Lookup 运算符。

    单行估计也是优化器停止寻找更好计划并返回 Good Enough Plan Found 消息的原因。单行估计的慢速计划的估计总成本仅为 0.013136 个成本单位，因此尝试找到更好的东西是没有意义的。当然，除了查找实际上返回 7,388,383 行而不是 1 行，导致相同数量的 Key Lookups。

    在大型表上保持最新和有用的统计数据可能很棘手，并且分区在这方面引入了其自身的挑战。我自己在跟踪标志 2389 和 2390 方面没有取得特别成功，但欢迎您测试它们。较新的 SQL Server 版本（R2 SP1 及更高版本）具有可用的动态统计更新，但仍未实现此按分区统计更新。同时，您可能希望在对该表进行重大更改时安排手动更新统计信息。

    对于这个特定的查询，我会考虑在编译快速查询计划期间实现优化器建议的索引：

    /*
The Query Processor estimates that implementing the following index could improve
the query cost by 98.8091%.

WARNING: This is only an estimate, and the Query Processor is making this 
recommendation based solely upon analysis of this specific query.
It has not considered the resulting index size, or its workload-wide impact,
including its impact on INSERT, UPDATE, DELETE performance.
These factors should be taken into account before creating this index.
*/
CREATE NONCLUSTERED INDEX [<Name of Missing Index>]
ON [dbo].[PDetail] ([ClientID],[PostedDate])
INCLUDE ([Amount]);

    索引应该是分区对齐的，带有一个ON PartitionSchemeName (PostedDate) 子句，但重点是提供明显最佳的数据访问路径将帮助优化器避免糟糕的计划选择，而无需求助于OPTIMIZE FOR UNKNOWN提示或使用局部变量等老式变通方法。

    随着索引的改进，检索Amount列的键查找将被消除，查询处理器仍然可以执行动态分区消除，并使用查找来查找特定ClientID和日期范围。

    • 29

  3. 我遇到了完全相同的问题，即存储过程变慢，OPTIMIZE FOR UNKNOWN并且RECOMPILE查询提示解决了缓慢并加快了执行时间。但是，以下两种方法并不影响存储过程的缓慢性： (i) 清除缓存 (ii) 使用 WITH RECOMPILE。所以，就像你说的，这不是真正的参数嗅探。

    跟踪标志 2389 和 2390 也没有帮助。只需更新统计信息 ( EXEC sp_updatestats) 就为我做了。

    • 0

相关问题