Q6.9: Stored Procedure Recompilation and Reresolution

When a stored procedure is created, the text is placed in syscomments and a parse tree is placed in sysprocedures. At this stage there is no compiled query plan.

A compiled query plan for the procedure only ever exists in memory (that is, in the procedure cache) and is created under the following conditions:

A procedure is executed for the first time.
A procedure is executed by a second or subsequent user when the first plan in cache is still in use.
The procedure cache is flushed by server restart or cache LRU flush procedure.
The procedure is executed or created using the with recompile option.

If the objects the procedure refers to change in some way - indexes dropped, table definition changed, etc - the procedure will be reresolved - which updates sysprocedures with a modified tree. Before 10.x the tree grows and in extreme cases the procedure can become too big to execute. This problem disappears in Sybase System 11. This reresolution will always occur if the stored procedure uses temporary tables (tables that start with "#").

There is apparently no way of telling if a procedure has been reresolved.

Traceflag 299 offers some relief, see Q1.4 for more information regarding traceflags.

The Official Explanation -- Reresolution and Recompilation Explained

When stored procedures are created, an entry is made in sysprocedures that contains the query tree for that procedure. This query tree is the resolution of the procedure and the applicable objects referenced by it. The syscomments table will contain the actual procedure text. No query plan is kept on disk. Upon first execution, the query tree is used to create (compile) a query plan (execution plan) which is stored in the procedure cache, a server memory structure. Additional query plans will be created in cache upon subsequent executions of the procedure whenever all existing cached plans are in use. If a cached plan is available, it will be used.

Recompilation is the process of using the existing query tree from sysprocedures to create (compile) a new plan in cache. Recompilation can be triggered by any one of the following:

First execution of a stored procedure,
Subsequent executions of the procedure when all existing cached query plans are in use,
If the procedure is created with the recompile option, CREATE PROCEDURE sproc WITH RECOMPILE
If execution is performed with the recompile option, EXECUTE sproc WITH RECOMPILE

Re-resolution is the process of updating the query tree in sysprocedures AND recompiling the query plan in cache. Re-resolution only updates the query tree by adding the new tree onto the existing sysprocedures entry. This process causes the procedure to grow in size which will eventually cause an execution error (Msg 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). Execution of a procedure that has been flagged for re-resolution will cause the re-resolution to occur. To reduce the size of a procedure, it must be dropped which will remove the entries from sysprocedures and syscomments. Then recreate the procedure.

Re-resolution can be triggered by various activities most of which are controlled by SQL Server, not the procedure owner. One option is available for the procedure owner to force re-resolution. The system procedure, sp_recompile, updates the schema count in sysobjects for the table referenced. A DBA usually will execute this procedure after creating new distribution pages by use of update statistics. The next execution of procedures that reference the table flagged by sp_recompile will have a new query tree and query plan created. Automatic re-resolution is done by SQL Server in the following scenarios:

Following a LOAD DATABASE on the database containing the procedure,
After a table used by the procedure is dropped and recreated,
Following a LOAD DATABASE of a database where a referenced table resides,
After a database containing a referenced table is dropped and recreated,
Whenever a rule or default is bound or unbound to a referenced table.

Forcing automatic compression of procedures in System 10 is done with trace flag 241. System 11 should be doing automatic compression, though this is not certain.

When are stored procedures compiled?

Stored procedures are in a database as rows in sysprocedures, in the form of parse trees. They are later compiled into execution plans.

A stored procedures is compiled:

with the first EXECute, when the parse tree is read into cache
with every EXECute, if CREATE PROCEDURE included WITH RECOMPILE
with each EXECute specifying WITH RECOMPILE
if the plans in cache for the procedure are all in use by other processes
after a LOAD DATABASE, when all procedures in the database are recompiled
if a table referenced by the procedure can not be opened (using object id), when recompilation is done using the table's name
after a schema change in any referenced table, including:
1. CREATE INDEX or DROP INDEX to add/delete an index
2. ALTER TABLE to add a new column
3. sp_bindefault or sp_unbindefault to add/delete a default
4. sp_bindrule or sp_unbindrule to add/delete a rule
after EXECute sp_recompile on a referenced table, which increments sysobjects.schema and thus forces re-compilation

What causes re-resolution of a stored procedure?

When a stored procedure references an object that is modified after the creation of the stored procedure, the stored procedure must be re-resolved. Re-resolution is the process of verifying the location of referenced objects, including the object id number. Re-resolution will occur under the following circumstances:

One of the tables used by the stored procedure is dropped and re-created.
A rule or default is bound to one of the tables (or unbound).
The user runs sp_recompile on one of the tables.
The database the stored procedure belongs to is re-loaded.
The database that one of the stored procedure's tables is located in is re-loaded.
The database that one of the stored procedure's tables is located in is dropped and re-created.

What will cause the size of a stored procedure to grow?

Any of the following will result in a stored procedure to grow when it is recompiled:

One of the tables used in the procedure is dropped and re-created.
A new rule or default is bound to one of the tables or the user runs sp_recompile on one of the tables.
The database containing the stored procedure is re-loaded.

Other things causing a stored procedure to be re-compiled will not cause it to grow. For example, dropping an index on one of the tables used in the procedure or doing EXEC WITH RECOMPILE.

The difference is between simple recompilation and re-resolution. Re-resolution happens when one of the tables changes in such a way that the query trees stored in sysprocedures may be invalid. The datatypes, column offsets, object ids or other parts of the tree may change. In this case, the server must re-allocate some of the query tree nodes. The old nodes are not de-allocated (there is no way to do this within a single procedure header), so the procedure grows. In time, trying to execute the stored procedure will result in a 703 error about exceeding the 64 page limit for a query.