运行存储过程的随机超时 - 删除重新创建修复
因此，我在一个已有 10 年历史的系统上使用一个较大的数据库（30 gig）sql 2005 和 .net 3.5 Web 前端。它有新的和旧的部分
我们遇到了一个越来越频繁发生的问题。
一个存储过程（到目前为止我们已经有 4 个不同的存储过程）决定它将超时。该调用是从网络服务器发生的，并达到 30 秒超时并记录到我们的错误日志中。该网站使用单一登录（我知道这是错误的，但由于遗留代码而无法更改）。
在此之后，我运行了完全相同的调用，并且需要（以我的身份登录）1秒。
问题仍然存在于这个存储过程上，直到我们删除并重新创建它，导致大量超时。每个 sp 调用都有不同的参数。 正如获取与当前用户相关的所有未签名轮班一样，因此当前用户作为参数传入
该解决方案有效，但我不太明白为什么。
我们的发布周期是两周，在此期间的任何时候都会发生此错误。它发生在发布后一周后的第二天，最后一次发生在发布后 12 天。
在每个版本中，我们都会对所有存储的过程/触发器/函数/视图进行 SQL 多脚本编写，每次删除并重新创建自身。
我所能想到的是存储过程执行计划已损坏/出错，并且删除重新创建它可以清除此问题。
我正在考虑使用 RECOMPILE 选项调用 sps，这是禁忌吗？或一个可接受的方法
So I work with a largish database (30 gig) sql 2005 with a .net 3.5 web front end on a 10 year old system. It has new and old bits
We are getting a problem that is happening more and more frequently.
A stored proc (we've had 4 different ones so far) decides that it will timeout. The call is happening from the webserver and hits the 30 sec timeout and logs to our error log. The website uses a single login (I know this is wrong but it cannot be changed due to legacy code).
Just after this I run the exact same call and it takes (logged in as me) 1 sec.
The issue remains on this one stored proc until we drop and recreate it, getting loads of timeouts. Each sp call has different parameters.
As in get me all the unsigned off shifts pertaining to the current user, so current user is passed in as a parameter
The solution works but I don't really understand why.
Our release cycle is two weeks and this error happens at anytime during it. It has happens the day after a release a week after the release and the last one was 12 days after the release.
Durign each release we SQL multi script all the stored procs/triggers/functions/views with each dropping and recreateing itself.
All I can think is that the stored proc execution plan has corrupted/gone wrong and dropping recreateing it clears this.
I am thinking of calling the sps WITH RECOMPILE option, is this a no-no?? or an acceptable way around
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这听起来非常像我一次又一次看到的问题 - 存储过程计划已从计划缓存中刷新，并且下次运行该过程时，恰好传递的参数会产生一个计划对于这组参数来说可能很好，但对于其他组合来说效果很差。
如果您有“可选”参数 - 可以传递 NULL 或 值，并且您在
WHERE子句中使用OR为了应对这个问题，通常会导致这种事情。使用 RECOMPILE 可能会导致每次都会使用大量 CPU 来编译计划 - 如果存储过程被多次调用，那么这很容易对服务器的总体性能产生影响 - 无论这个操作系统是否比坏的影响更重要计划是另一回事。
一般来说，最好尝试重写查询 - 如果您使用 OR 来处理不同的参数集，那么动态 SQL（以正确的方式完成，使用带有参数的 sp_executesql）会很有帮助。
PS 关于存储过程在运行时工作正常 - 我也看到过这一点 - 我希望它会生成不同的计划 - 我的怀疑一直是运行例如 SSMS 有一组略有不同的
SET选项比（在我的实例中）.Net 启用 - 并且计划在此实例中单独缓存。如果有人可以验证这可能发生，我们将不胜感激！This sounds very much like a problem I've seen time and time again - where the stored procedure plan has been flushed from the plan cache and the next time the procedure is run it just so happens that the parameters passed in result in a plan that is probably great for that set of parameters but which performs awfully for other combinations.
If you've 'optional' parameters - where NULL or a value may be passed through, and you're using
ORin theWHEREclause to cope with this then that's usually going to lead to this sort of thing.WITH RECOMPILE may result in a lot of CPU going to compiling the plan each time - if the stored procedure is called a lot then this could easily have an effect on the general performance of your server - whether this os outweighed by the effect of a bad plan is another matter.
In general, it's better to try to rewrite the query - if you are using
ORs to cope with diffferent sets of parameters then dynamic SQL (done the right way, using sp_executesql with parameters) can help a lot.P.S. Regarding the stored procedure working fine when you run it - I've seen this too - I expect that it's down to getting a different plan generated - my suspicion has always been that running through e.g. SSMS has a slightly different set of
SEToptions enabled than (in my instance) .Net - and the plans are cached separately in this instance. If anyone can validate that this may happen it'd be appreciated!我怀疑是存储过程直接导致了这种情况，除非它正在执行某种未被清理的锁定/事务逻辑。即便如此，我也看不出删除/重新创建对此有何影响。我可能会查看 SP 正在使用的数据，并尝试使用分析来跟踪其执行路径，看看是否可以提高性能。
I would doubt that it is the stored procedure that is directly causing this, unless it is performing some kind of locking/transaction logic that is not being cleaned up. Even then, I cant see how dropping/recreating would have any affect on this. I would probably look at the data the SP is using and attempt to trace the execution path of this using profiling and see if the performance can be improved.
这很可能是为特定过程存储的错误执行计划的结果。
问题（简化）是 SQL Server 尝试根据传递的参数优化执行计划的使用。在某些情况下，这可能会导致可怕的性能。
这是一些阅读内容以进一步解释它。
http://blogs.msdn.com/b/queryoptteam /archive/2006/03/31/565991.aspx
http://elegantcode. com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
从好的方面来说，通过复制传递的参数来修复它非常简单proc 到局部变量。
This is most probably a result of an bad execution plan stored for the specific proc.
The problem (simplified) is that SQL server tries to optimize the usage of execution plans based on the parameters passed. This can then lead to horrendous performance in some cases.
Heres some reading to explain it further.
http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx
http://elegantcode.com/2008/05/17/sql-parameter-sniffing-and-what-to-do-about-it/
On the bright side, its very simple to fix by copying the passed parameters in the proc to local variables.