SQL存储过程执行计划性能不佳——参数嗅探

我有一个存储过程，它接受日期输入，如果没有传入值，则稍后将其设置为当前日期：

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

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

同样令人困惑的是，即使使用的 @MyDate 的值是，生成的执行计划也很糟糕。actually NULL（并且没有设置为CURRENT_TIMESTAMP通过 IF 语句）

我发现禁用参数嗅探（通过欺骗参数）可以解决我的问题：

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

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

有谁了解为什么会发生这种情况？

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

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