BUG: Large Number of Procedure Plans in Cache for Subprocedures

• Microsoft SQL Server version 6.5

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          : kbbug6.50 
Version           : WINNT:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 22, 1999