为什么SqlServer优化器与参数如此混淆? [英] Why does the SqlServer optimizer get so confused with parameters?
问题描述
我知道这与参数嗅探有关，但我感到困惑的是，即使使用一项能够很好地完成许多复杂工作的技术，以下示例中的内容也可能成为可能.
I know this has something to do with parameter sniffing, but I'm just perplexed at how something like the following example is even possible with a piece of technology that does so many complex things well.
我们中的许多人都遇到了存储过程，该过程断断续续地比平时慢几个数量级，然后，如果您从过程中复制sql并在单独的查询窗口中使用相同的参数值，它将以最快的速度运行像往常一样.
Many of us have run into stored procedures that intermittently run several of orders of magnitude slower than usual, and then if you copy out the sql from the procedure and use the same parameter values in a separate query window, it runs as fast as usual.
我只是通过转换此过程来修复了这样的过程:
I just fixed a procedure like that by converting this:
alter procedure p_MyProc
(
@param1 int
) as -- do a complex query with @param1
对此:
alter procedure p_MyProc
(
@param1 int
)
as
declare @param1Copy int;
set @param1Copy = @param1;
-- Do the query using @param1Copy
它从运行一分钟的时间缩短到不到一秒，就像通常运行的那样.这种行为似乎是完全随机的.对于10个@ param1输入中的9个，查询很快，无论最终需要处理多少数据或结果设置了多大.但是对于十分之一的人，它只是迷路了.解决方法是在查询中将int替换为相同的int?
It went from running in over a minute back down to under one second, like it usually runs. This behavior seems totally random. For 9 out of 10 @param1 inputs, the query is fast, regardless of how much data it ends up needing to crunch, or how big the result set it. But for that 1 out of 10, it just gets lost. And the fix is to replace an int with the same int in the query?
这没有道理.
@gbn链接到此问题，其中详述了类似的问题:
@gbn linked to this question, which details a similar problem:
已知问题? :SQL Server 2005存储过程无法使用参数完成
我犹豫地哭了臭虫！"因为那通常是个问题，但是对我而言，这确实像是个小虫.当我使用相同的输入运行存储过程的两个版本时，我看到了相同的查询计划.唯一的区别是，原始文件需要花费一分钟多的时间才能运行，而带有高飞参数复制的版本则可以立即运行.
I hesitate to cry "Bug!" because that's so often a cop-out, but this really does seem like a bug to me. When I run the two versions of my stored procedure with the same input, I see identical query plans. The only difference is that the original takes more than a minute to run, and the version with the goofy parameter copying runs instantly.
推荐答案
十分之一的缓存计划有误.
The 1 in 10 gives the wrong plan that is cached.
RECOMPILE增加了开销，使用掩码可以根据每个优点(非常简单)评估每个参数.
RECOMPILE adds an overhead, masking allows each parameter to be evaluated on it's own merits (very simply).
按错误的计划，如果十分之之一对索引1进行扫描，而其他9对索引2进行寻道怎么办?例如，十分之1占行的50％?
By wrong plan, what if the 1 in 10 generates an scan on index 1 but the other 9 produce a seek on index 2? eg, the 1 in 10 is, say, 50% of the rows?
其他问题
- Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter
- Stored Procedure failing on a specific user
重新编译不起作用，因为在编译时会嗅探参数.
通过其他链接(粘贴):
Recompile does not work because the parameters are sniffed at compile time.
From other links (pasted in):
此文章解释了...
...parameter values are sniffed during compilation or recompilation...
最后(编辑3):
当时，参数嗅探可能是个好主意，并且在大多数情况下可能效果很好.我们将其用于所有以WHERE子句结尾的参数. 我们不需要使用它，因为我们知道只有少数几个(例如更复杂的报告或许多参数)可能会引起问题，但是我们使用它是为了保持一致性.
Parameter sniffing was probably a good idea at the time and probably works well mostly. We use it across the board for any parameter that will end up in a WHERE clause. We don't need to use it because we know that only a few (more complex eg reports or many parameters) could cause issues but we use it for consistency.
当用户抱怨时它会回来并咬我们的事实，我们应该使用掩膜...
And the fact that it will come back and bite us when the users complain and we should have used masking...
这篇关于为什么SqlServer优化器与参数如此混淆?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
