June 6, 2005 at 9:14 am
I have a reasonable big stored procedure that runs fine 80% of the time, the other 20% it times out. The only way I have managed to fix this is by dropping and re-creating the stored proc in which case it will be fine for a day or two. Any suggestions or ideas as to why the stored proc would be "corrupting" would be greatly appreciated.
extra information:
sql server 2000 sp 3a, windows 2k server
stored proc returns 3 result sets
June 6, 2005 at 9:37 am
I am thinking that by droping and recreating the sp you are effectivley recompiling it. The execution plan it is using is probably out of date, using old stats etc. I would look at whether the stats and indexes on you base is up to date and if not come up with a solution to remedy that. If all seems fine maybe it would be applicable to force a recompile of the sp, but this takes overhead. Maybe if it only gets antsy over a period of time you could schedule a recompile.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply