June 16, 2005 at 8:30 am
I have a couple of stored procs that are periodically (once or twice a week) bogging down. The procs were originally structured as follows:
IF (condition xxx)
(SELECT query A)
ELSE
(SELECT query B)
I thought that maybe the structure was causing the wrong query execution plan to be used (e.g. query A executed using query B's plan), so I restructured the SP's as follows to separate the execution plans:
IF (condition xxx)
(EXEC SP1 which runs query A)
ELSE
(EXEC SP2 which runs query B)
The problem is now coming back. One of our DBA's has created a job that recompiles the SP's a couple of times a day, but this is a band-aid and not a real solution. The called SP's (SP1 and SP2) each contain one rather involved SELECT statement each, using a couple of parameters - nothing special, & they perform satisfactorily most of the time.
Any ideas as to why the periodic slowing down might be happening, or where I can start looking ? Thanks!
June 16, 2005 at 8:36 am
Try to catch the parameters that cause the wrong execution plan. You'll most likely see a big difference in teh execution plan (scan instead of seek).
Do a search on parameter sniffing on this and you'll find help.
June 17, 2005 at 9:27 am
We ran across something similar, but it had to do with the volatility of the data in the database causing the statistics to get out-of-date. We simply ran sp_updatestats periodically through the day, and the problem vanished.
June 20, 2005 at 7:04 am
Thanks, that's an interesting topic, & it may be what we're dealing with. I love "undocumented features", don't you? :^)
June 20, 2005 at 7:12 am
It's not undocumented. It's just the way it works, and it can cause problems sometimes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply