And So I make use of a largish database (30 gig) sql 2005 having a .internet 3.5 web front-end on the ten year old system. It's new and old bits

We're obtaining a problem that's happening increasingly more frequently.

A saved proc (we have had 4 different styles to date) decides that it'll timeout. The phone call is going on in the webserver and hits the 30 sec timeout and logs to the error log. The web site utilizes a single login (I understand this really is wrong however it can't be transformed because of legacy code).

Soon after i run the identical call also it takes (drenched in as me) 1 sec.

The problem remains on that one saved proc until we drop and recreate it, getting lots of timeouts. Each sp call has different parameters. As with get me all of the unsigned off changes relating to the present user, so current user is passed in like a parameter

The answer works however i don't really realise why.

Our release cycle is 2 days which error happens anytime throughout it. It's happens the next day of a to produce week following the release and also the 4g iphone was 12 days following the release.

Durign each release we SQL multi script all of the saved procs/triggers/functions/sights with every shedding and recreateing itself.

All I'm able to think would be that the saved proc execution plan has corrupted/gone wrong and shedding recreateing it clears this.

I'm considering calling the sps WITH RECOMPILE option, is a no-no?? or perhaps an acceptable way around

This sounds greatly just like a problem I have seen again and again - in which the saved procedure plan continues to be flushed in the plan cache and subsequently time the process is run it so happens the parameters passed in create a plan that's most likely great for your group of parameters but which works awfully for other combinations.

If you have 'optional' parameters - where NULL or something might be passed through, and you are using OR within the WHERE clause to handle this then that's usually likely to result in this kind of factor.

WITH RECOMPILE may result in many CPU likely to producing the program every time - when the saved procedure is known as a great deal than the could easily impact the overall performance of the server - whether this os outweighed by the consequence of bad plan's another matter.

Generally, it's better to try and rewrite the query - if you work with ORs to handle diffferent teams of parameters then dynamic SQL (done the proper way, using sp_executesql with parameters) can be very convenient.

P.S. Concerning the saved procedure working fine advertising media are it - I have seen that as well - I expect it's lower for you to get another plan produced - my suspicion happens to be that running through e.g. SSMS includes a slightly different group of SET options enabled than (during my instance) .Internet - and also the plans are cached individually in cases like this. If anybody can validate this can happen it would be appreciated!

I'd doubt that it's the saved procedure that's directly leading to this, unless of course it's carrying out some type of securing/transaction logic that's not cleared up. Even so, I cant observe how shedding/re-creating might have any relation to this. I'd most likely consider the data the SP is applying and try to trace the execution path of the using profiling if the performance could be enhanced.

This really is most most likely a direct result an bad execution plan saved for that specific proc.

The issue (simplified) is the fact that SQL server attempts to optimize using execution plans in line with the parameters passed. This could then result in terrible performance in some instances.

Heres some reading through to describe 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/

Around the good side, its quite simple to repair by copying the passed parameters within the proc to local variables.