Generally SQL server recompile stored proc when
(1)You told SQL server to do so explicitly by
(a)The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.
(b)Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed.
(c)You can force the stored procedure to be compiled by specifying the WITH RECOMPILE option when you execute the stored procedure.
(2)New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.
(3)SET Statements That Change Session Options
Changing the value of five session options with the SET statement cause a recompile. The options are: ANSI_DEFAULTS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YEILDS_NULL.
(4) Any structural changes made to a table or view referenced by the stored proc (ALTER TABLE and ALTER VIEW).
|
