Automatic Query Plan Recompilation
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
• When 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 ...
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
