recompiling stored procedures in SQL Server

When you perform operations such as adding an index or changing data in an indexed column, you should recompile the original query plan that accesses the database table to optimize it. This optimization is performed automatically the first time a stored procedure is run after a Microsoft SQL Server 2005 reboot. This optimization is also performed when the underlying table used by the stored procedure changes. However, if you add a new index that the stored procedure might benefit from, the optimization will not be performed automatically until the next time Microsoft SQL Server restarts and then runs the stored procedure. In this case, it is useful to force the next time the stored procedure is executed to recompile.

If necessary, other reasons for forcing a stored procedure to recompile can impede the "parameter lookup" behavior compiled by the stored procedure. When SQL Server executes a stored procedure, any parameter values that the procedure uses at compile time are included as part of the build query plan. If these values are typical values that are used when the stored procedure is later invoked, the stored procedure will benefit from the query plan each time it is compiled and executed. Otherwise, performance may be affected.

Attention:

SQL Server 2005 introduces the ability to perform statement-level recompilation on stored procedures. When SQL Server 2005 compiles a stored procedure, only the statements that cause the recompilation are compiled without compiling the entire procedure. Therefore, when SQL Server rebuilds the query plan, it uses the value of the parameter in the recompiled statement. These values may differ from those that were originally passed to the procedure.

Force recompile of stored procedures

In SQL Server, there are three ways to force a stored procedure to be recompiled:

sp_recompile system stored Procedures force the next time the stored procedure is executed to recompile it.

Specifies the WITH RECOMPILE option in its definition when the stored procedure is created, indicating that SQL Server will not cache the plan for the stored procedure and recompile it each time the stored procedure is executed. The WITH RECOMPILE option is used when the parameter values of a stored procedure differ significantly between executions, resulting in the need to create a different execution plan each time. This option is not commonly used because it must be recompiled every time the stored procedure is executed, which can cause the execution of the stored procedure to slow down.

If you only want to execute a single query in the stored procedure that you want to recompile instead of the entire stored procedure, specify the RECOMPILE query hint in each query that you want to recompile. This behavior is similar to the SQL Server statement-level recompilation described earlier, but in addition to using the current parameter value of the stored procedure, the RECOMPILE query hint also uses the value of the local variable in the stored procedure when compiling the statement. Use this option only if you are using atypical or temporary values in a query sub set that is part of a stored procedure. For more information, see Query TIPS (Transact-SQL).

You can force a stored procedure to be recompiled when it is executed by specifying the WITH RECOMPILE option. Use this option only if the supplied parameter is not a typical parameter, or if the data has changed significantly since the stored procedure was created.

Attention:

If the object referenced by the stored procedure is deleted or renamed, an error is returned when the stored procedure is executed. However, if you replace the object referenced in the stored procedure with an object of the same name, you can execute the stored procedure without having to re-create it.

Recompile the stored procedure the next time it is run

SP_RECOMPILE (Transact-SQL)