BUG: Large Number of Procedure Plans in Cache for Subprocedures (195684)
The information in this article applies to:
This article was previously published under Q195684
BUG #: 18283 (SQLBUG_65)
SYMPTOMS
When a stored procedure is executed with the SET SHOWPLAN option on, a new
query plan is generated for each execution of the procedure. This is
expected behavior. When SHOWPLAN is set off, existing plans in the
procedure cache will be reused, if available, upon re-execution of the
procedure. However, if the procedure calls subprocedures, new plans will be
generated for each of the subprocedures on each execution, even after the
SHOWPLAN option has been set off. This can lead to excessive plans stored
in cache for the subprocedures.
New plans for the subprocedure are created even if the procedure is called
from a separate connection from where the showplan was issued; it is not
connection-specific. However, this will only occur when the subprocedure is
invoked through the calling procedure. If the subprocedure is executed
directly, it will not generate a new plan unless there is no existing plan
available for use.
WORKAROUND
To work around this problem, execute the calling procedure with the WITH
RECOMPILE option. This will stop subsequent executions from creating new
plans for the subprocedures. Existing plans for the subprocedure will
remain in procedure cache until they are flushed out.
STATUS
Microsoft has confirmed this to be a problem in SQL Server version 6.5.
MORE INFORMATION
The large number of plans in cache, in and of itself, is not a cause for
concern. The excess plans will be flushed out of cache when the memory is
needed for other requests. However, the performance benefit of avoiding
parse and compile time for the subprocedure will be lost as a new plan is
generated for each subprocedure when the calling procedure is executed.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbnofix KB195684 |
|---|
|