Microsoft KB Archive/195684

= BUG: Large Number of Procedure Plans in Cache for Subprocedures =

Article ID: 195684

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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.

Additional query words: DBCC MEMUSAGE Number Trees Plans sub st proc stproc sproc

Keywords: kbbug kbnofix KB195684

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.