November 27, 2009 at 4:29 am
What about the table that the procedure reads from? Does it change frequently? Some times when data changes enormously, the statistics becomes stale, the execution plan also becomes less suitable.
November 27, 2009 at 5:08 am
Joe
Thanks for your reply...
Indexes and Stats are updated on these table every night.
JL
November 27, 2009 at 5:46 am
When the procedure executes for the first time it's execution plan is stored in the procedure cache. Now, that execution plan is only really relevant for the parameters that were passed at that point in time.
If differing values are passed through to the stored procedure, then the execution plan that was generated originally won't necessarily be optimal for the current execution.
Also, as already mentioned, if the data in the table (and subsequently statistics) change then this could knock out the cached execution plan. It's all well and good that you update statistics each night, but if a large amount of changes occur during the day then your cached plans might be sub-optimal until the next time your update stats, indexes, etc.
You could create the stored procedure with the recompile option so that it always recompiles when it runs. Often the benefits of having a stored execution plan are outweighed by recompiling if the data changes sufficiently.
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply