June 26, 2011 at 3:05 pm
Hi all, 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
June 26, 2011 at 5:46 pm
amjid.qureshi (6/26/2011)
Hi all, 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 bitsWe 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
Let me as you... which is worse... a forced recompile that may take up to a second or two or unpredictable timeouts?
The problem you're fighting is known as "Parameter Sniffing" and, if you do a search on Google, you'll find several methods to prevent it without using a recompile. Of course, recompiling is one of the methods that some will recommend. It all "Depends".
--Jeff Moden
Change is inevitable... Change for the better is not.
June 27, 2011 at 3:15 pm
1) could be parameter sniffing, as Jeff alluded to.
2) could be blocking that is keeping your sproc from running sometimes. use sp_whoisactive to watch for this. amazing freebie!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply