Reason a Stored Procedure is recompiled

Background:

I was investigating the fact that 'Table variables result in fewer re-compilations of a stored procedure as compared to temporary tables' which is mentioned in my another post 'Temp Table in SQL Server'. Than found two very good references (References-1 & 2) and felt like consolidating into fewer lines.

Query Plan related to Stored Procedure – Steps:

1. The Stored Procedure is executed
2. Check in cache to see it’s available
3. If not available in cache,
1. Load the Stored Procedure
2. Compile the query plan
3. Save compiled plan in cache
4. Reuse by subsequent calls until the plan is invalidated and forced to recompile

Reason a Stored Procedure is recompiled:

1. Use of a WITH RECOMPILE clause in the CREATE PROCEDURE or EXECUTE statement.
2. Schema changes to any of the referenced objects, including adding or dropping constraints, defaults, or rules.
3. Running sp_recompile for a table referenced by the procedure.
4. Restoring the database containing the procedure or any of the objects the procedure references (if you are performing cross-database operations).
5. Sufficient server activity causing the plan to be aged out of cache
6. A sufficient percentage of data changes in a table that is referenced by the stored procedure
1. Ways to counteract recompilation

                                                              i.      Execute the statement using sp_executesql - Statements executed using the sp_executesql stored procedure are not compiled as part of the stored procedure plan

                                                             ii.      Use sub-procedures to execute the statements that are causing the recompilations - the statement may still cause a recompilation, but instead of recompiling the large calling stored procedure, it will only recompile the small sub-procedure

                                                           iii.      Use the KEEP PLAN option

7. The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.
8. The procedure performs certain operations on temporary tables
1. Ways to counteract recompilation

                                                              i.      All statements that contain the name of a temporary table refer to a temporary table created in the same stored procedure, and not in a calling or called stored procedure, or in a string executed using the EXECUTE statement or sp_executesql stored procedure

                                                             ii.      All statements that contain the name of a temporary table appear syntactically after the temporary table in the stored procedure or trigger.

                                                           iii.      There are no DECLARE CURSOR statements whose SELECT statements reference a temporary table

                                                          iv.      All statements that contain the name of any temporary table precede any DROP TABLE statement that references a temporary table

References:

1. URL - http://support.microsoft.com/kb/243586
2. URL -  http://support.microsoft.com/kb/305977