Microsoft SQL Server 2012 Bible

Query Recompiles

Because the Execution Plan is based on a combination of the SQL statement, the indexes, and the data, a number of changes might cause SQL Server to decide that the execution plan is no longer valid, or no longer the optimal plan. SQL Server then marks the Execution Plan as invalid and generates a new Query Execution Plan the next time it executes.

Single statement query recompiles simply cause the query to recompile the next time it's executes. But stored procedures can recompile midstream.

Following are the most common causes of an automatic recompile:

• Updated Statistics. If statistics for data referenced by the query update, SQL Server blocks execution and recompiles based on the newer statistics. If asynchronous statistics updates occur, the update occurs in the background. The current query does not block, but it uses the out-of-date statistics for the current execution.
• Trivial plans. SQL Server cannot cache execution plans for reuse if the plan has a single best method for execution. An example is a SELECT * on a table that does not include a predicate.
• A large change in the number of rows in the base table referenced by the query (if AUTO_UPDATE of statistics is enabled), even rows inserted by a trigger.
• Mixing DML statements and DDL statements in a stored procedure can cause a recompile. For example, creating a temp table, running an update, and then creating another temp table forces a recompile of the stored procedure following the second temp table's ...