运行存储的过程的随机超时-删除重新创建修复程序 [英] Random timeout running a stored proc - drop recreate fixes
问题描述
因此，我使用的是大型数据库（30 gig）sql 2005，该数据库在10年历史的系统上具有.net 3.5 Web前端。它有新的和旧的
我们遇到的问题越来越频繁。
A存储过程（到目前为止，我们有4个不同的过程）决定它将超时。该呼叫是从Web服务器进行的，并达到30秒超时，并记录到我们的错误日志中。该网站使用一次登录（我知道这是错误的，但是由于旧代码而无法更改）。
就在此之后，我运行了完全相同的调用，并花了1秒钟（以我的身份登录）。
问题一直存在于此存储的过程中，直到我们删除并重新创建它，从而获得大量超时。每个sp调用具有不同的参数。
与让我了解所有与当前用户有关的未签署班次一样，因此将当前用户作为参数传入
解决方案有效，但我不这样做
我们的发布周期为两周，因此在此期间的任何时间都会发生此错误。它发生在发布后一周的第二天，而最后一个发布是发布后的12天。
对每个发布都加注密码，我们会用SQL多脚本将所有存储的过程/ triggers / functions / views，每次删除并重新创建自己。
我正在考虑将sps WITH RECOMPILE选项调用，这是不行吗？还是可以接受的方式
这听起来很像是我一次又一次看到的问题-存储过程计划已从计划缓存中清除，并且下次运行该过程时，恰好发生了这样的情况，即传入的参数所生成的计划可能对该参数集很有用，但对于其他组合却表现不佳。
如果您具有可选参数-可能会传递NULL 或值，并且您使用的是 OR 中的 WHERE 子句来解决这个问题，那么通常会导致这种情况。
由于RECOMPILE可能导致大量CPU每次都要编译计划-如果多次调用存储过程，那么这很容易影响服务器的整体性能-无论是否超过不好的计划的效果是另一回事。
总的来说，这更好尝试重写查询-如果您使用 OR s来处理不同的参数集，则动态SQL（正确的方法，将sp_executesql与参数一起使用）可以帮助您
PS关于存储过程在您运行时能否正常运行-我也已经看到了-我希望这取决于生成另一个计划的原因-我一直怀疑运行过程例如SSMS启用的 SET 选项集与.Net（在我的实例中）略有不同-并且计划在此实例中单独缓存。如果有人可以验证这种情况的发生，将不胜感激！
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
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 OR in the WHERE clause 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 SET options 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!
这篇关于运行存储的过程的随机超时-删除重新创建修复程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
