sql-server-2008 – 参数嗅探与VARIABLES vs重新编译vs OPTIMIZE

发布时间：2020-12-12 05:55:29 所属栏目：MsSql教程来源：网络整理

导读：所以我们今天早上有一个长时间运行的触发器导致问题(30秒运行时间).我们决定检查参数嗅探是否应该受到指责.因此,我们重写了proc并将传入参数设置为变量,以便打败参数嗅探.一种尝试/真实的方法. Bam,查询时间得到改善(不到1秒).查看查询计划时,在原始未使用的

所以我们今天早上有一个长时间运行的触发器导致问题(30秒运行时间).我们决定检查参数嗅探是否应该受到指责.因此,我们重写了proc并将传入参数设置为变量,以便打败参数嗅探.一种尝试/真实的方法. Bam,查询时间得到改善(不到1秒).查看查询计划时,在原始未使用的索引中找到了改进.

为了验证我们没有得到误报,我们在原始proc上做了一个dbcc freeproccache,然后重新查看改进后的结果是否相同.但是,令我们惊讶的是,原来的进程仍然很慢.我们再次尝试使用WITH RECOMPILE,仍然很慢(我们尝试重新编译对proc的调用以及它自己的proc内部).我们甚至重新启动了服务器(显然是dev框).

所以,我的问题是这个……当我们在一个空的计划缓存上得到相同的慢查询时,参数嗅探怎么可能是责备…应该没有任何参数来snif ???

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

在进一步测试中,我们还发现在proc DID的内部插入OPTION(OPTIMIZE FOR UNKNOWN)可以获得预期的改进计划.

所以,你们中的一些人比我聪明,你能否提供一些线索,了解幕后发生的这类结果？

另一方面,由于GoodEnoughPlanFound的原因,快速计划也提前中止,而快速计划在实际计划中没有早期中止的原因.

综上所述

>从传入参数中创建变量(1秒)
>重新编译(30秒)
> dbcc freeproccache(30秒)
>选项(英国优化)(1秒)

更新：

请参阅此处的慢执行计划：https://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

请参阅此处的快速执行计划：https://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

注意：出于安全原因,表,架构,对象名称已更改.

解决方法

查询是

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

该表包含103,129,000行.

ClientId在快速计划中查找日期的剩余谓词,但需要执行96次查找以检索金额. < 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”有关于使用局部变量的说法

Using local variables to defeat parameter sniffing is a fairly common
trick,but the OPTION (RECOMPILE) and OPTION (OPTIMIZE FOR) hints
… are generally more elegant and slightly less risky solutions

Note

In SQL Server 2005,statement level compilation allows for compilation
of an individual statement in a stored procedure to be deferred until
just before the first execution of the query. By then the local
variable’s value would be known. Theoretically SQL Server could take
advantage of this to sniff local variable values in the same way that
it sniffs parameters. However because it was common to use local
variables to defeat parameter sniffing in SQL Server 7.0 and SQL
Server 2000+,sniffing of local variables was not enabled in SQL
Server 2005. It may be enabled in a future SQL Server release though
which is a good reason to use one of the other options outlined in
this chapter if you have a choice.

从快速测试到此结束,上述行为在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,因此这不是典型的参数嗅探(其中计划针对一组值进行编译,然后针对另一组值运行).

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

您可能会遇到描述here和here所述的升序日期的问题.对于包含1亿行的表,您需要在SQL Server自动更新统计信息之前插入(或以其他方式修改)2000万行.似乎上次更新它们的零行与查询中的日期范围匹配,但现在有700万.

您可以安排更频繁的统计信息更新,考虑跟踪标志2389 – 90或使用OPTIMIZE FOR UKNOWN,这样它就会回到猜测而不是能够使用日期时间列上当前误导性的统计信息.

在下一版本的SQL Server(2012年之后)中可能不需要这样做. A related Connect item包含有趣的响应

Posted by Microsoft on 8/28/2012 at 1:35 PM
We’ve done a cardinality
estimation enhancement for the next major release that essentially
fixes this. Stay tuned for details once our previews come out. Eric

Benjamin Nevarez在本文末尾对此进行了改进：

A First Look at the New SQL Server Cardinality Estimator.

在这种情况下,似乎新的基数估计量将回落并使用平均密度,而不是给出1行估计.

有关2014年基数估算和升序关键问题的一些其他详细信息：

New functionality in SQL Server 2014 – Part 2 – New Cardinality Estimation

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!