Database Reference
In-Depth Information
Figure 17-11.
Extended Events output showing a stored procedure recompilation because of a
SET
option change
If the procedure were reexecuted, you wouldn't see a recompile since those are now part of the execution plans.
Since
SET NOCOUNT
doesn't change the environment settings, unlike the
SET
statements used to change the ANSI
settings as shown previously,
SET NOCOUNT
doesn't cause stored procedure recompilation. I explain how to use
SET NOCOUNT
in detail in Chapter 18.
Execution Plan Aging
SQL Server manages the size of the procedure cache by maintaining the age of the execution plans in the cache, as
you saw in Chapter 15. If a stored procedure is not reexecuted for a long time, the age field of the execution plan can
come down to 0, and the plan can be removed from the cache because of memory pressure. When this happens and
the stored procedure is reexecuted, a new plan will be generated and cached in the procedure cache. However, if there
is enough memory in the system, unused plans are not removed from the cache until memory pressure increases.
Explicit Call to sp_recompile
SQL Server automatically recompiles queries when the schema changes or statistics are altered enough. It also
provides the
sp_recompile
system stored procedure to manually mark entire stored procedures for recompilation.
This stored procedure can be called on a table, view, stored procedure, or trigger. If it is called on a stored procedure
or a trigger, the stored procedure or trigger is recompiled the next time it is executed. Calling
sp_recompile
on a table
or a view marks all the stored procedures and triggers that refer to the table/view for recompilation the next time they
are executed.
