sql-server-2008 – 参数嗅探与VARIABLES vs从头编译vs OPTIMIZE

以是我们本日早上有一个长时刻运行的触发器导致题目(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包括风趣的相应

（编辑：湖南网）

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