SQL 糟糕的存储过程执行计划性能——参数嗅探 [英] SQL poor stored procedure execution plan performance - parameter sniffing

问题描述

我有一个存储过程，它接受一个日期输入，如果没有传入任何值，该输入将被设置为当前日期:

创建程序 MyProc@MyDate 日期时间 = NULL作为如果 @MyDate 为 NULL SET @MyDate = CURRENT_TIMESTAMP-- 使用@MyDate 做一些事情

我遇到了问题，如果在第一次编译存储过程时将 @MyDate 作为 NULL 传入，则所有输入值的性能总是很糟糕(NULL 或其他)，而如果在编译存储过程时传入日期/当前日期，则所有输入值(NULL 或其他)的性能都很好.

同样令人困惑的是，即使使用的@MyDate 的值实际上 NULL(并且没有设置为CURRENT_TIMESTAMP 由 IF 语句)

我发现禁用参数嗅探(通过欺骗参数)可以解决我的问题:

创建程序 MyProc@MyDate 日期时间 = NULL作为声明@MyDate_Copy DATETIMESET @MyDate_Copy = @MyDate如果@MyDate_Copy 是 NULL SET @MyDate_Copy = CURRENT_TIMESTAMP-- 使用@MyDate_Copy 做一些事情

我知道这与参数嗅探有关，但是我看到的所有参数嗅探变坏"的示例都涉及使用传入的非代表性参数编译的存储过程，但是在这里我我看到执行计划对于 SQL 服务器可能认为参数在执行语句的点可能采用的所有可能的值都很糟糕 - NULL、CURRENT_TIMESTAMP 或其他.

有没有人了解为什么会发生这种情况?

解决方案

基本上是的 - SQL Server 2005 的参数嗅探(在某些补丁级别中)严重损坏.我见过一些计划实际上永远不会完成(在小数据集上几小时内)，即使是小(几千行)数据集，一旦参数被屏蔽，这些数据集在几秒钟内完成.这是在参数始终为相同数字的情况下.我要补充一点，在我处理这个问题的同时，我发现 LEFT JOIN/NULLs 没有完成的很多问题，我用 NOT IN 或 NOT EXISTS 替换了它们，这解决了计划以完成一些事情.同样，一个(非常差的)执行计划问题.在我处理这个问题时，DBA 不会给我 SHOWPLAN 访问权限，并且自从我开始屏蔽每个 SP 参数以来，我没有任何进一步的执行计划问题，我必须深入研究这个问题才能完成.

在 SQL Server 2008 中，您可以使用 OPTIMIZE FOR UNKNOWN.

I have a stored procedure that accepts a date input that is later set to the current date if no value is passed in:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

I'm having problems whereby if @MyDate is passed in as NULL when the stored procedure is first compiled, the performance is always terrible for all input values (NULL or otherwise), wheras if a date / the current date is passed in when the stored procedure is compiled performance is fine for all input values (NULL or otherwise).

What is also confusing is that the poor execution plan that is generated in is terrible even when the value of @MyDate used is actually NULL (and not set to CURRENT_TIMESTAMP by the IF statement)

I've discovered that disabling parameter sniffing (by spoofing the parameter) fixes my issue:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

I know this is something to do with parameter sniffing, but all of the examples I've seen of "parameter sniffing gone bad" have involved the stored procedure being compiled with a non-representative parameter passed in, however here I'm seeing that the execution plan is terrible for all conceivable values that SQL server might think the parameter might take at the point where the statement is executed - NULL, CURRENT_TIMESTAMP or otherwise.

Has anyone got any insight into why this is happening?

解决方案

Basically yes - parameter sniffing (in some patch levels of) SQL Server 2005 is badly broken. I have seen plans that effectively never complete (within hours on a small data set) even for small (few thousand rows) sets of data which complete in seconds once the parameters are masked. And this is in cases where the parameter has always been the same number. I would add that at the same time I was dealing with this, I found a lot of problems with LEFT JOIN/NULLs not completing and I replaced them with NOT IN or NOT EXISTS and this resolved the plan to something which would complete. Again, a (very poor) execution plan issue. At the time I was dealing with this, the DBAs would not give me SHOWPLAN access, and since I started masking every SP parameter, I've not had any further execution plan issues where I would have to dig in to this for non-completion.

In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.

这篇关于SQL 糟糕的存储过程执行计划性能——参数嗅探的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！