SQL Server Performance Forum – Threads Archive

When is it needed to recompile an stored procedure

Hello Whenever I change the code of an stored procedure it is required to recompile it? In any case, which are the recommended actions to take after the code of an stored procedure is changed, so that it always runs as fast as possible? Thanks a lot.

When you execute a stored procedure for the first time, the SQL Server query optimizer builds an execution plan for the stored procedure, so that it can run quickly without needing to repeat the parsing, optimizing, and compiling steps each time it is executed. Reusing the execution plan is one of the main advantages of using the stored procedures. However, the execution plan is not stored in memory permanently. Because the stored procedure execution plan can be outdated, for example when a large amount of data modifications are made to a table referenced by a stored procedure, you may need to recompile the execution plan. SQL Server 2000 automatically recompiles the stored procedure execution plan when one of the following conditions are met:
Any schema changes of the objects referenced in the stored procedure were made.
An index used by the execution plan of the stored procedure is dropped.
A large amount of data modifications are made to a table referenced by a stored procedure.
The new distribution statistics were generated.
The execution plan was deleted from memory, because the memory is required for other objects.
A table has trigger(s) and the number of rows in the inserted or deleted tables grows significantly. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>