Recompile

When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure’s query plan for those changes. Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes.

Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance.

SQL Server features statement-level recompilation of procedures. When SQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure. If certain queries in a procedure regularly use atypical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries. If you have nested stored procedures, the recompile hint only applies to code in the outermost procedure. You can still get parameter sniffing on any calls to sub-procedures within the stored procedure itself.

Using RECOMPILE in the stored procedure header is pretty drastic — the procedure won’t cache an execution plan when it runs. This means:

• No execution plans in cache to review
• No execution stats recorded in sys.dm_exec_query_stats
• No execution stats recorded in sys.dm_exec_procedure_stats

There are a few ways to ensure recompilation of a stored procedure:

• using WITH RECOMPILE,
• making the stored procedure dynamic (think exec())
• Marking the proc for recompile with sp_recompile.
• changing the schema that a cached query plan relies upon
• calling DBCC FREEPROCCACHE
• At the query level an individual statement within a proc can be recompiled with the RECOMPILE query hint (SQL 2008).

Factors in Recompilation

Besides the hard-factors listed above, what causes stored procedure recompilation? Well, lots of things. Some of these are interwoven with the list above, but I want to re-present them b/c it might not be obvious.

• Inserting or deleting lots of data (data density in indexes & tables often controls query plans)
• Rebuilding indexes (a change to underlying objects)
• Creating/dropping temp tables (again, underlying DML changes).
• query plan ages out (think not used recently and sql want's to clean up memory use)

The SP:Recompile event identifies the reason for the recompilation with an integer code in the EventSubClass column.

EXEC sp_recompile 'test_rec';