SQL存储过程执行计划性能不佳——参数嗅探
我有一个存储过程，它接受一个日期输入，如果没有传入任何值，该日期输入稍后将设置为当前日期：
CREATE PROCEDURE MyProc
@MyDate DATETIME = NULL
AS
IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
-- Do Something using @MyDate
如果 @MyDate 作为 NULL< 传入，我会遇到问题/code> 首次编译存储过程时，对于所有输入值（NULL 或其他），性能总是很糟糕，而如果在编译存储过程时传入日期/当前日期对于所有输入值（NULL 或其他），性能都很好。
同样令人困惑的是，即使使用的 @MyDate 的值实际上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 或其他。
有谁了解为什么会发生这种情况？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
基本上是的 - SQL Server 2005 的参数嗅探（在某些补丁级别中）已严重损坏。 我见过实际上永远不会完成的计划（在小数据集上几小时内），即使对于小数据集（几千行），一旦参数被屏蔽，这些数据集在几秒钟内完成。 这是在参数始终为相同数字的情况下。 我想补充一点，在处理这个问题的同时，我发现了很多 LEFT JOIN/NULL 未完成的问题，我用 NOT IN 或 NOT EXISTS 替换了它们，这将计划解决为可以完成的事情。 再次，一个（非常糟糕的）执行计划问题。 在我处理这个问题时，DBA 不会给我 SHOWPLAN 访问权限，而且自从我开始屏蔽每个 SP 参数以来，我没有遇到任何进一步的执行计划问题，我必须深入研究这个问题以防止未完成。
在 SQL Server 2008 中，您可以使用
OPTIMIZE FOR UNKNOWN。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 Server 2005）中解决这个问题的一种方法是添加查询优化器提示，而不是仅仅通过重新声明本地参数来屏蔽参数。
这是一篇很好的博客文章，详细讨论了它：
SqlServer 2005 中的参数嗅探
我使用了： OPTION (优化 (@p = '-1'))
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.
Here is a good blog post that talks more about it:
Parameter Sniffing in SqlServer 2005
I used: OPTION (optimize for (@p = '-1'))
在过程内部声明过程参数，并将外部参数传递给内部..编译..
Declare the procedure parameter inside the procedure and pass the external parameter to the internal .. compile ..