SQL Server Execution Plans:

SQL Server Execution Plans:

If the following criteria are met, the plan is removed from memory:
1. more memory is required by the system
2. the "age" of the plan has reached zero
3. the plan isn't currently being referenced by an existing connection.

Execution plans are not sacrosanct. Certain events and actions can cause a plan to be recompiled. It is important to remember this, because recompiling execution plans can be a very expensive operation.
The following actions can lead to recompilation of an execution plan:
1. changing the structure or schema of a table referenced by the query
2. changing an index used by the query
3. dropping an index used by the query
4. updating the statistics used by the query
5. calling the function, sp_recompile
6. subjecting the keys in tables referenced by the query to a large number of Inserts or deletes (which leads to statistics changes)
7. for tables with triggers, significant growth of the inserted or deleted tables
8. mixing DDL and DML within a single query, often called a deferred compile
9. changing the SET options within the execution of the query
10. changing the structure or schema of temporary tables used by the query
11. changes to dynamic views used by the query
12. changes to cursor options within the query
13. changes to a remote rowset, like in a distributed partitioned view
14. when using client-side cursors, if the FOR BROWSE options are changed.