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

因此，我们有一个长时间运行的进程，导致今天早晨（30秒+运行时间）出现问题。我们决定检查是否应该归因于参数嗅探。因此，我们重写了proc并将输入的参数设置为变量，以消除参数嗅探。一种尝试/正确的方法。 Bam，查询时间得到改善（不到1秒）。在查看查询计划时，在原始索引未使用的索引中发现了改进。

只是为了验证我们没有得到误报，我们在原始proc上执行了dbcc freeproccache，重新运行以查看改善的结果是否相同。但是，令我们惊讶的是，原始过程仍然运行缓慢。我们再次使用WITH RECOMPILE进行了尝试，但速度仍然很慢（我们在对proc的调用以及在proc自身内部尝试了重新编译）。我们甚至重新启动了服务器（很明显是dev框）。

所以，我的问题是...当我们在空的计划缓存上收到相同的慢查询时，如何将参数嗅探归咎于... snif应该没有任何参数？？？？

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

在进一步的测试中，我们还发现，在proc DID的内部插入OPTION（OPTIMIZE FOR UNKNOWNN）可以获得预期的改进计划。

那么，你们中有些人比我聪明，您能提供一些线索来了解产生这种结果的幕后情况吗？

另一方面，慢速计划也因原因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

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

#1 楼

该查询为

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实用故障排除》一书中提到使用局部变量

使用局部变量消除参数嗅探是很常见的
/>技巧，但OPTION (RECOMPILE)和OPTION (OPTIMIZE FOR)提示
...通常是更优雅且风险较小的解决方案

注意

在SQL Server中2005年，语句级别的编译允许将存储过程中单个语句的编译推迟到第一次执行查询之前。届时，将知道local
变量的值。从理论上讲，SQL Server可以利用它的优势来嗅探局部变量值，就像嗅探参数一样。但是，由于在SQL Server 7.0和SQL
Server 2000+中通常使用local
变量来克服参数嗅探，因此在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亿行的表，您需要在SQL Server自动为您更新统计信息之前插入（或修改）2000万。似乎上次更新它们的行中有零行与查询中的日期范围匹配，但现在有700万行。

您可以安排更频繁的统计信息更新，考虑使用跟踪标志2389 - 90或使用OPTIMIZE FOR UKNOWN，这样它就落了重新猜测，而不是能够使用datetime列上的当前令人误解的统计信息。

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

Microsoft在2012年8月28日下午1:35发布
我们已经完成了基数估计
基本上可以解决此问题的下一个主要版本。预览发布后，请继续关注细节。 Eric

Benjamin Nevarez在文章末尾看到了2014年的改进：

首先了解了新的SQL Server基数估计器。
/>
在这种情况下，新的基数估算器似乎会回退并使用平均密度，而不是给出1行的估算值。

有关2014基数估算器的一些其他详细信息以及此处的升序关键问题：

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

#2 楼

所以，我的问题是...当我们在空的计划缓存上收到相同的慢查询时，怎么应该怪参数嗅探呢？不应该有任何参数吗？嗅探吗？

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

无论如何，这都是重点，正如马丁·史密斯（Martin Smith）指出的那样，因为参数嗅探不是问题。编译慢查询时，统计信息表明@BeginDate和@EndDate的嗅探值没有行：

嗅探值是最近的，表明马丁提到的上升关键问题。由于估计按日期查找的索引仅返回单行，因此优化程序选择一个计划，将ClientID上的谓词作为残差推入Key Lookup运算符。
优化程序停止寻找更好的计划的原因，返回“找到足够好的计划”消息。单行估算的慢计划的估算总成本仅为0.013136成本单位，因此毫无意义地寻找更好的方案。当然，除了查找实际上返回7,388,383行，而不是返回一行，从而导致相同数量的键查找。

统计信息可能很难保持最新，并且在大型表上很有用，分区在这方面带来了自身的挑战。我自己在跟踪标记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和日期范围。

希望我可以将两个答案标记为正确，但是再次感谢您提供的其他信息-非常有启发性。

–RThomas
13年1月29日在16:01

自从我发布此消息已经有几年了...但是我只是想让您知道。我一直都在用“不完全理解”这个词，当我这样做时，我总是想到保罗·怀特。每次让我笑。

–RThomas
2015年2月9日在17:53

#3 楼

我遇到了一个完全相同的问题，即存储过程变慢了，并且OPTIMIZE FOR UNKNOWN和RECOMPILE查询提示解决了该问题，并缩短了执行时间。
但是，以下两种方法并没有影响存储过程的速度：
（i）使用WITH RECOMPILE清除缓存（ii）。因此，就像您说的那样，这实际上不是参数嗅探。

跟踪标志2389和2390也没有帮助。只是更新统计信息（EXEC sp_updatestats）对我来说是成功的。

编程黑洞网

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

#1 楼

#2 楼

评论

#3 楼