SQL Server attempts to reuse existing execution plans for stored procedures, but certain operations cause the execution plans to become inefficient or invalid. In these cases, a new execution plan needs to be recompiled on the next execution of the stored procedure. The following conditions cause a plan to be invalidated:
• Whenever there is a change to the schema of a referenced table or view
• When an index for a referenced table is dropped or changed
• When the statistics used by an execution plan have been updated, either explicitly or automatically
sp_recompile has been run on a table referenced by a stored procedure
• When a sufficient number of data changes have been made to a table that is referenced ...