INF: Conditions for Stored Procedure Recompilation

ID: Q104445


The information in this article applies to:


SUMMARY

This article provides information on the conditions under which a stored procedure execution plan is recompiled and other conditions under which the query tree stored in SYSPROCEDURES can be re-resolved.


MORE INFORMATION

When a stored procedure is created, a normalized query tree for the procedure is saved in the SYSPROCEDURES system table. The normalized query tree contains the referenced table/view and column information. The process of normalization parses the SQL statements into a more efficient format and resolves all referenced objects into their internal representations. During this process, table names, for example, are resolved into their object IDs and column names are resolved into column IDs. When the procedure is subsequently executed, the tree is retrieved from SYSPROCEDURES, and based on it, an optimized execution plan is created and stored in the procedure cache.

Under certain conditions, the execution plan in the procedure cache can become invalid; as a result, the procedure needs to be recompiled. Under other conditions, it is possible that the query tree in SYSPROCEDURES can become invalid; in this case the query tree needs to be re-resolved.

The following are the conditions under which a stored procedure is recompiled:

  1. An index on a referenced table is dropped.


  2. The table is altered using ALTER TABLE.


  3. A rule or default is bound to the table or column.


  4. The stored procedure has been flagged for recompilation by executing sp_recompile on any referenced table.


  5. The stored procedure is executed using the WITH RECOMPILE option.


  6. The stored procedure is created using the WITH RECOMPILE option.


  7. All copies of the execution plan in cache are currently in use.


Item 1 causes the "indexdel" column in SYSOBJECTS to be incremented. Items 2 through 4 cause the "schema" column in SYSOBJECTS for the object to be incremented.

When a stored procedure is executed, it checks these columns for all referenced objects to see if there has been any change since the last execution. If a change is detected, the stored procedure is recompiled.

The following matrix summarizes what actions cause the "schema" or "indexdel" columns in SYSOBJECTS to be updated for a referenced object:

   Action                   Schema    Indexdel
   -------------------------------------------
   Drop Index                 -           X
   Bind / Unbind rule         X           -
   Bind / Unbind default      X           -
   Alter Table                X           -
   Update Statistics          -           -
   Create Index               -           -
   sp_recompile               X           - 

Note that adding an index or updating statistics does not update the schema; thereby, it does not force a new execution plan. An existing plan in the cache will continue to be used unless the procedure is executed with the WITH RECOMPILE command or has been created with the RECOMPILE command option.

The following are the conditions under which the tree in SYSPROCEDURES can be re-resolved:
  1. Executing LOAD DATABASE on the database containing the procedure.


  2. Executing LOAD DATABASE on a database containing a referenced table.


  3. Dropping and re-creating a table referenced by the procedure.


  4. Dropping and re-creating a database containing a table referenced by the procedure.


Note that while recompiling will not cause the procedure to grow, the re-resolution of the query tree can. In some cases this may eventually cause the stored procedure execution plan to become too big and generate error message 703:
Memory request failed because more than 64 pages are required to run the query in its present form. The query should be broken up into shorter queries if possible.

DBCC MEMUSAGE can be used to monitor the size of execution plans for stored procedures.( DBCC MEMUSAGE also displays the size of the tree but only when the stored procedure is created. Subsequently only the size of the execution plan is displayed. )

The following is some more information on execution plans:

Additional query words: recompilation re-resolution Windows NT


Keywords          : kbother SSrvServer SSrvWinNT 
Version           : 4.2 6.0 6.5 | 4.2 6.0 6.5
Platform          : OS/2 WINDOWS 
Issue type        : 

Last Reviewed: March 17, 1999