Query Recompilation - SQL Server Query Performance Tuning - page 325

Figure 17-9. Extended Events output showing a stored procedure recompilation because of a regular table
You can see that the SELECT statement is recompiled when it's executed the second time. Dropping the table
within the stored procedure during the first execution doesn't drop the query plan saved in the plan cache. During the
subsequent execution of the stored procedure, the existing plan includes the processing strategy for the table. However,
because of the re-creation of the table within the stored procedure, SQL Server considers it a change to the table schema.
Therefore, SQL Server recompiles the statement within the stored procedure before executing the SELECT statement
during the subsequent execution of the rest of the stored procedure. The value of the recompile_clause for the
corresponding sql_statement_recompile event reflects the cause of the recompilation.
Recompilation Because of a Local Temporary Table
Most of the time in the stored procedure you create local temporary tables instead of regular tables. To understand
how differently the local temporary tables affect stored procedure recompilation, modify the preceding example by
just replacing the regular table with a local temporary table.
IF (SELECT OBJECT_ID('dbo.TestProc')
) IS NOT NULL
DROP PROC dbo.TestProc;