INF: Conditions for Stored Procedure Recompilation

  - Microsoft SQL Server version 4.2, 6.0 and 6.5  
  - Microsoft SQL Server version 4.2 for OS/2

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.

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           -

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.

   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.

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.

  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

  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.