Execution Plan re-Compilation Time

References:

Cache and reuse of execution plans

Re-compile the execution plan

Depending on the new status of the database, some changes in the database may cause the execution plan to be less efficient or invalid. SQL server detects changes that make the execution plan invalid and marks the plan as invalid. After that, you must re-compile the new plan for the next connection that executes the query. Plan invalidation may be caused:

1. Alter table and alter view ).
2. Modify any indexes used in the execution plan.
3. Update the statistical information used by the execution plan. The update may be automatically generated or displayed in the statement (such as update statistics.
4. Delete the index used by the execution plan.
5. Explicit callSp_recompile.
6. A large number of key changes (modifications made by other users to the tables referenced by the query using the insert or delete Statement ).
7. For tables with triggers,InsertedOrDeletedThe number of rows in the table increases significantly.
8. Use the with recompile option to execute the stored procedure.

To make the statements correct or obtain a query execution plan that may be faster, most of them need to be re-compiled.

In SQL Server 2000, as long as the statements in the batch processing result in re-compilation, the entire batch processing will be re-compiled, regardless of whether the batch processing is through the storage process, trigger, ad hoc batch query, or submit through a predefined statement. In SQL Server 2005, only statements that cause re-compilation in batch processing will be re-compiled. Due to this difference, the recompilation count in SQL Server 2000 and SQL Server 2005 cannot be compared. In addition, because SQL Server 2005 extends the feature set, more recompilation types are available.

Statement-level re-compilation helps improve performance, because in most cases, only a few statements lead to re-compilation and related losses (CPU time and lock ). Therefore, the loss of other statements that do not need to be re-compiled in batch processing is avoided.

SQL Server ProfilerSP: recompileTrace events report statement-level re-compilation in SQL Server 2005. This trace event only reports batch recompilation in SQL Server 2000. In addition, in SQL Server 2005,TextdataColumn. Therefore, you no longer need to track SQL Server 2000SP: stmtstartingOrSP: stmtcompletedTo obtain the result of re-Compilation of the transact-SQL text.

SQL Server 2005 also adds a new tracking event calledSQL: stmtrecompileIt reports statement-level re-compilation. This tracking event can be used for tracking and debugging re-compilation.SP: recompileOnly generated for stored procedures and triggers, whileSQL: stmtrecompileQueries and uses stored procedures, triggers, and ad hoc batch queries.Sp_executesqlExecuted batch processing, prepared queries, and dynamic SQL generation.

SP: recompileAndSQL: stmtrecompileOfEventsubclassThe column contains an integer code to indicate the reason for re-compilation. The following table shows the meaning of each code number.

Eventsubclass Value	Description
1	The architecture has been changed.
2	The statistics have been changed.
3	Compilation has been delayed.
4	The Set option has been changed.
5	The temporary table has been changed.
6	The remote row set has been changed.
7	The for browse permission has been changed.
8	The query notification environment has changed.
9	The partition view has been changed.
10	The cursor option has been changed.
11	Option (recompile) requested ).

Note: When the auto_update_statistics database option is set to on, if the query targets tables or index views, the statistical information of the table or index view has been updated since the last execution or the base has changed significantly. The query will be re-compiled. This behavior applies to standard user-defined tables, temporary tables, and DML triggers InsertedAnd DeletedTable. If too much re-compilation affects the query performance, consider changing this setting to off. When the auto_update_statistics database option is set to off, no re-compilation will occur due to changes in statistics or base numbers. However, InsertedAnd DeletedExcept tables. Because these tables are TempdbTherefore, whether to re-compile the query to access these tables depends on TempdbIn auto_update_statistics. Note that in SQL Server 2000, even if this parameter is set to off, the query is still based on the DML trigger. InsertedAnd DeletedThe base changes of the table are re-compiled. For more information about disabling auto_update_statistics, see index statistics.