SQL差的存储过程执行计划性能 - 参数嗅探

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).

我遇到问题，如果在首次编译存储过程时将@MyDate作为NULL传入，则对于所有输入值（NULL或其他），性能总是很糟糕，如果日期/当前日期传入时存储过程是编译的，对所有输入值都是好的（NULL或其他）。

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)

令人困惑的是，即使使用@MyDate的值实际为NULL（并且IF语句未设置为CURRENT_TIMESTAMP），生成的糟糕执行计划也很糟糕

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.

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

Has anyone got any insight into why this is happening?

有没有人知道为什么会这样？

2 个解决方案

#1

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.

基本上是 - 参数嗅探（在一些补丁级别）SQL Server 2005严重破坏。我已经看到计划实际上永远不会完成（在小数据集上的几小时内），即使是小的（几千行）数据集，一旦参数被屏蔽，它们在几秒钟内完成。这是在参数始终为相同数字的情况下。我想补充一点，在我处理这个问题的同时，我发现LEFT JOIN / NULL没有完成时会遇到很多问题，我用NOT IN或NOT EXISTS替换了它们，这解决了计划完成的问题。同样，（非常糟糕的）执行计划问题。在我处理这个问题的时候，DBA不会给我SHOWPLAN访问权限，因为我开始屏蔽每个SP参数，所以我没有任何进一步的执行计划问题，我将不得不深入研究这个问题。。

In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.

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

#2

One way I was able to get around this problem in (SQL Server 2005) instead of just masking the parameters by redeclaring local parameters was to add query optimizer hints.

我能够在（SQL Server 2005）中解决此问题而不是通过重新声明本地参数来屏蔽参数的一种方法是添加查询优化器提示。

Here is a good blog post that talks more about it: Parameter Sniffing in SqlServer 2005

这是一篇很好的博客文章，详细介绍了它：SqlServer 2005中的参数嗅探

I used: OPTION (optimize for (@p = '-1'))

我用过：OPTION（针对（@p =' - 1'）进行优化）

#1