3

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

Amjid Qureshi
  • 577
  • 1
  • 8
  • 19
  • Had a same issue today, very tricky. Executing alter statement without change helped me also. – Muflix May 03 '18 at 13:25

3 Answers3

3

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!

Will A
  • 24,780
  • 5
  • 50
  • 61
0

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.

ChrisBint
  • 12,773
  • 6
  • 40
  • 62
  • " I can't see how dropping/recreating would have any affect on this". It won't have any effect if the performance issue is locking/transaction logic but it will have a massive effect if the issue is Execution Plan related. Recreating the SP (ALTER or DROP/CREATE) will flush the current execution plan forcing a new one to be generated on the next call. This can make a huge difference. – Chris Walsh Jan 30 '15 at 14:21
0

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.

bic
  • 2,201
  • 26
  • 27