INF: Conditions for Stored Procedure Recompilation

ID: Q104445

The information in this article applies to:

Microsoft SQL Server versions 4.2x, 6.0, 6.5

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:

An index on a referenced table is dropped.

The table is altered using ALTER TABLE.

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

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

The stored procedure is executed using the WITH RECOMPILE option.

The stored procedure is created using the WITH RECOMPILE option.

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:

Executing LOAD DATABASE on the database containing the procedure.

Executing LOAD DATABASE on a database containing a referenced table.

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

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:

Stored procedures are re-usable, not re-entrant, for example, only one user may be executing a given copy of the execution plan at one time. If two or more users try to execute the same procedure at the same time, the server will create an additional copy of the execution plan. On the other hand, when a user finishes using a procedure the execution plan is available for reuse, in cache, by another user.

If an existing plan is in use, it is possible that a new execution plan for a procedure may be different from an older one:
- If the user passes a different set of parameters in the second invocation.
- If an index has been added to a referenced table or update statistics has been run at some time since the older execution plan was generated.
For stored procedures that accept parameters that can be used in the WHERE clause, the execution plan in the procedure cache is the optimized path to the data (based on the parameters given during the first execution of the procedure). For such procedures, if the parameters can be significantly different for each execution of the procedure, it is probably worth exploring the use of the WITH RECOMPILE option during execution or even during the creation of the procedure. This does add the overhead of having to generate the execution plan for each execution of the procedure.

The additional execution plans stay in cache until they are paged out, and could cause one execution to be very different from another. A user has no control or knowledge of which plan will be used. This can sometimes explain unexpectedly different execution times for the same procedure given the same data and parameters

If a very large number of stored procedures are being executed concurrently and repetitively, then performance could be improved by enlarging procedure cache. This will prevent execution plans from constantly being paged out of cache and having to read the procedure tree from disk and recompile it.

Additional query words: recompilation re-resolution Windows NT

Keywords : kbother SSrvServer SSrvWinNT
Version : winnt:4.2x,6.0,6.5
Platform : winnt
Issue type : kbinfo