SQL Server过程执行计划的奇怪问题
我想知道你们是否可以帮助我解决我最近在 SQL Server 上遇到的一个奇怪问题。
我有一个存储过程（让我们调用 SPold），它相当大，需要进行大量计算（不可能在应用程序中执行此操作，因为大约 6000 个用户的信息需要一次性返回（我根据姓氏将其减少到 1000））。存储过程通常在几秒钟内执行，并且每几分钟调用一次。
今天早上，存储过程的执行时间突然增加了 4-10 倍，导致多次超时。我发现通过使用新名称 (SPnew) 复制该过程并执行，我将再次获得快速执行时间。这向我表明执行计划是原始 SPold 的问题，因此我决定通过重新编译来执行它。这会更快地返回结果（尽管不如 SPnew 快），但用户对 SPold 的后续调用再次变慢。就好像新计划没有被保留一样。
我所做的就是解决这个问题，将 Exec SPnew 放入 SPold 中，现在对 SPold 的调用再次快速返回。
有谁知道这里发生了什么事吗？唯一一夜更新的是统计数据，尽管我认为这应该会影响 SPold 和 SPnew。
I was wondering if you guys could help me get to the bottom of a weird problem I have recently had on SQL Server.
I have a stored procedure (lets call SPold) which is reasonably large with a lot of calculations (can't possibly do this in app as info for around 6000 users needs to come back in a one-er (I reduce this to 1000 based on Surname)). The stored procedure usually executes in a couple of seconds, and is called once every couple of minutes.
Now this morning, the stored procedure was suddenly taking 4-10 times as long to execute, causing a number of timeouts. I discovered that by making a copy of the procedure with a new name (SPnew) and executing, I would get the fast execution times again. This indicated to me that the execution plan was the problem with the original, SPold, so I decided to execute it with recompile. This would return the results a quicker (although not as fast as SPnew), but subsequent calls from users to SPold were once again slow. It was like the new plan wasn't being kept.
What I have done is to fix this is put Exec SPnew into SPold, and now calls to SPold are returning fast again.
Does anyone have any idea what is going on here? The only thing that updated overnight was the statistics, although I think that this should affect both SPold and SPnew.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我在 Sql Server 2005 中也遇到过两个“奇怪”的案例，这可能也与您的问题有关。
在第一种情况下，当作为 dbo 运行时，我的程序执行得非常快，而当在不同的用户帐户下从应用程序运行时，速度很慢。
在第二种情况下，过程的查询计划针对首次调用该过程所使用的参数值进行了优化，随后该计划也被重新用于其他参数值，从而导致执行速度缓慢。
对于第二种情况，解决方案是将参数值复制到过程中的局部变量中，然后在查询中使用变量而不是参数。
I've also encounterred two "strange" cases with Sql Server 2005, which might relate to your problem as well.
In the first case my procedure executed prety fast, when being run as dbo, and it was slow when being run from the application, under a different user account.
In the second case the query plan of the procedure got optimized for the parameter values with which the procedure was called for the first time, and this plan was then reused later for other parameter values as well, resulting in a slow execution.
For this second case the solution was to copy the parameter values into local variables in the procedure, and then using the variables in the queries instead of the parameters.
听起来您由于参数嗅探而遇到了错误缓存的查询计划。
你能发布存储过程吗？
SQL Server 2005 中的批量编译、重新编译和计划缓存问题
我闻到了一个参数！
在 SQL Server 2005 中，您可以使用 OPTIMIZE FOR 参数首选值的查询提示，以解决与参数嗅探相关的一些问题：
尽管 SQL Server 2005 不支持 OPTIMIZE FOR UNKNOWN（在 SQL Server 2008 中引入），但
将消除对给定参数的参数嗅探：
在 SQL Server 2005 中，您可以通过将参数复制到局部变量中，然后在查询中使用该局部变量来实现相同的效果。
Sounds like you are experiencing an incorrectly cached query plan due to parameter sniffing.
Can you post the stored procedure?
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005
I Smell a Parameter!
In SQL Server 2005, you can use the OPTIMIZE FOR query hint for preferred values of parameters to remedy some of the problems associated with parameter sniffing:
Although SQL Server 2005 does not support OPTIMIZE FOR UNKNOWN (introduced in SQL Server 2008) which
will eliminate parameter sniffing for a given parameter:
You can achieve the same effect in SQL Server 2005 by copying the parameter into a local variable, and then use the local variable in the query.