SQL SQL糟糕的存储过程执行计划性能——参数嗅探
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1007397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL poor stored procedure execution plan performance - parameter sniffing
提问by Justin
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 @MyDateis passed in as NULLwhen the stored procedure is first compiled, the performance is always terrible for all input values (NULLor otherwise), wheras if a date / the current date is passed in when the stored procedure is compiled performance is fine for all input values (NULLor 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 actuallyNULL(and not set to CURRENT_TIMESTAMPby the IF statement)
同样令人困惑的是，即使 @MyDate 使用的值实际上是NULL（而不是CURRENT_TIMESTAMP由 IF 语句设置的），生成的糟糕的执行计划也很糟糕
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_TIMESTAMPor otherwise.
我知道这与参数嗅探有关，但是我看到的所有“参数嗅探变坏”的示例都涉及使用传入的非代表性参数编译的存储过程，但是在这里我看到的是执行计划是可怕的SQL Server可能觉得参数可能需要在其中执行该语句的点所有可能的值- NULL，CURRENT_TIMESTAMP或以其他方式。
Has anyone got any insight into why this is happening?
有没有人了解为什么会发生这种情况？
采纳答案by Cade Roux
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/NULLs 没有完成的很多问题，我用 NOT IN 或 NOT EXISTS 替换了它们，这解决了计划完成的事情。同样，一个（非常差的）执行计划问题。在我处理这个问题的时候，DBA 不会给我 SHOWPLAN 访问权限，因为我开始屏蔽每个 SP 参数，我
In SQL Server 2008 you can use OPTIMIZE FOR UNKNOWN.
在 SQL Server 2008 中，您可以使用OPTIMIZE FOR UNKNOWN.
回答by Matt Palmerlee
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'））
回答by MjkSoft
Declare the procedure parameter inside the procedure and pass the external parameter to the internal .. compile ..
在procedure内部声明procedure参数，将外部参数传递给内部..compile..
