Recompilation

The process of creating a new query plan from the existing query tree is called recompilation. Recompilation takes place whenever one of the following events occurs:

• The procedure is loaded from disk to the procedure cache; after you restart SQL Server, the first execution will always be a new compilation.
• An index on a table referenced in the procedure is dropped.
• All copies of the execution plan in cache are currently in use; a new procedure must be loaded from disk and compiled.
• A procedure is created using the WITH RECOMPILE option.
• A procedure is executed using the WITH RECOMPILE option.
• A procedure is flagged by the SA for recompilation using the sp_recompile system stored procedure.

In addition, the CREATE INDEX statement causes automatic recompilation of stored procedures the next time they are executed. (Note that the UPDATE STATISTICS statement does not cause recompilation.) Dropping an index or table referenced by a query invalidates all existing cached copies of the query plan.