SQL Server中的参数嗅探(或欺骗)

不久之前,我有一个查询,我为我的一个用户运行了很多.它仍然在进化和调整,但最终它稳定并且运行得非常快,所以我们从中创建了一个存储过程.

到目前为止,这是正常的.

但是,存储过程很慢.查询和proc之间没有实质性差异,但速度变化很大.

[后台,我们正在运行SQL Server 2005.]

友好的本地DBA(不再在这里工作)看了一下存储过程并说"参数欺骗!" (编辑:虽然它似乎也可能被称为'参数嗅探',这可能解释了当我试图搜索它时谷歌的点击率很低.)

我们将一些存储过程抽象到第二个存储过程,将对这个新内部过程的调用包装到预先存在的外部过程中,称为外层过程,嘿,它与原始查询一样快.

那么,是什么给出的？有人可以解释参数欺骗吗？

奖金积分

强调如何避免它

建议如何识别可能的原因

讨论替代策略,例如统计数据,指数,关键,以减轻这种情况

Brent Ozar.. 53

仅供参考 - 当您使用SQL 2005并使用参数存储过程时,您需要了解其他内容.

SQL Server将使用所使用的第一个参数编译存储过程的执行计划.所以如果你运行这个:

usp_QueryMyDataByState 'Rhode Island'

执行计划最适合小型州的数据.但如果有人转身跑步:

usp_QueryMyDataByState 'Texas'

针对德克萨斯州大小的数据,针对Rhode-Island大小的数据设计的执行计划可能效率不高.当服务器重新启动时,这会产生令人惊讶的结果,因为新生成的执行计划将针对首先使用的任何参数 - 不一定是最好的参数.该计划不会重新编译,直到有充分理由这样做,就像重建统计数据一样.

这就是查询计划的用武之地,SQL Server 2008提供了许多新功能,可以帮助DBA长期定位特定的查询计划,无论首先调用哪些参数.

我担心的是,当您重建存储过程时,您强制执行计划重新编译.你用你最喜欢的参数调用它,然后它当然很快 - 但问题可能不是存储过程.可能是存储过程在某些时候使用一组不寻常的参数重新编译,因此,查询计划效率低下.您可能没有修复任何问题,并且下次服务器重新启动或重新编译查询计划时可能会遇到同样的问题.

1> Brent Ozar..：

---

仅供参考 - 当您使用SQL 2005并使用参数存储过程时,您需要了解其他内容.

SQL Server将使用所使用的第一个参数编译存储过程的执行计划.所以如果你运行这个:

usp_QueryMyDataByState 'Rhode Island'

执行计划最适合小型州的数据.但如果有人转身跑步:

usp_QueryMyDataByState 'Texas'

针对德克萨斯州大小的数据,针对Rhode-Island大小的数据设计的执行计划可能效率不高.当服务器重新启动时,这会产生令人惊讶的结果,因为新生成的执行计划将针对首先使用的任何参数 - 不一定是最好的参数.该计划不会重新编译,直到有充分理由这样做,就像重建统计数据一样.

这就是查询计划的用武之地,SQL Server 2008提供了许多新功能,可以帮助DBA长期定位特定的查询计划,无论首先调用哪些参数.

我担心的是,当您重建存储过程时,您强制执行计划重新编译.你用你最喜欢的参数调用它,然后它当然很快 - 但问题可能不是存储过程.可能是存储过程在某些时候使用一组不寻常的参数重新编译,因此,查询计划效率低下.您可能没有修复任何问题,并且下次服务器重新启动或重新编译查询计划时可能会遇到同样的问题.

---

首先,您可以为每个参数声明局部变量,将参数复制到这些变量中,并在查询中使用这些变量,而不是直接使用参数.我不知道为什么这有效,它闻起来但它解决了问题(使用Sql Server 2008).其次,查看OPTIMIZE FOR UNKNOWN查询提示.您可以在MSDN上阅读有关它和其他提示:http://msdn.microsoft.com/en-us/library/ms181714.aspx

---

拿起Grant Fritchey的优秀书籍SQL Server 2008查询性能蒸馏,他会通过你所有的选择.虽然它说2008年,但它对2005年来说也很棒.

2> nkav..：

---

是的,我认为你的意思是参数嗅探,这是SQL Server优化器用来试图找出参数值/范围的技术,因此它可以为您的查询选择最佳执行计划.在某些情况下,SQL Server在参数嗅探方面表现不佳,并且没有为查询选择最佳执行计划.

我相信这篇博客文章 http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx有一个很好的解释.

您的示例中的DBA似乎选择选项#4将查询移动到另一个sproc到单独的过程上下文.

您也可以在原始sproc上使用with recompile或在参数上使用optimize for选项.

---

+1,但请注意"with recompile"本身可能存在性能问题.我倾向于首先尝试选项#4 ......

3> 6eorge Jetso..：

---

加快速度的一种简单方法是在sproc的最开始将输入参数重新分配给本地参数,例如

CREATE PROCEDURE uspParameterSniffingAvoidance
    @SniffedFormalParameter int
AS
BEGIN

    DECLARE @SniffAvoidingLocalParameter int
    SET @SniffAvoidingLocalParameter = @SniffedFormalParameter

    --Work w/ @SniffAvoidingLocalParameter in sproc body 
    -- ...

---

@Cory我不相信你说的是准确的.它可以防止嗅探,但不会阻止计划被缓存.它只会导致一个通用的计划(IE不是特定的参数值)来代替.