RECOMPILE OPTION IN STORED PROCEDURES IN SQL SERVER 2005

Reasons when recompilation occurs are :-
Inserting column to a table or view.
Dropping column to a table or view.
Inserting constraints to a table.
Dropping constraints to a table.
Inserting an indexing to a table or view.
Inserting a trigger to a table.
Dropping a trigger to a table.

If the database is changed by any reason like adding new indexes or modifying data in the indexed columns , the original query plans should be optimized again by recompiling them.

This optimization happens automatically the first time a stored procedure is run after Microsoft SQL Server 2005 is restarted.It also occurs if an underlying table used by the stored procedure changes. But if a new index is added from which the stored procedure might benefit, optimization does not happen until the next time the stored procedure is run after Microsoft SQL Server is restarted.

In this situation, it can be useful to force the stored procedure to recompile the next time it executes.

Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is called subsequently, then the stored procedure benefits from the query plan each time it compiles and executes. If not, performance may suffer.

SQL Server provides three ways to force a stored procedure to recompile:

1 ) The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

2 ) 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. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon and causes the stored procedure to execute more slowly, because the stored procedure must be recompiled each time it is executed.
If you only want individual queries inside the stored procedure to be recompiled, rather than the entire stored procedure, specify the RECOMPILE query hint inside each query you want recompiled. This behavior mimics SQL Server’s statement-level recompilation behavior noted above, but in addition to using the stored procedure’s current parameter values, the RECOMPILE query hint also uses the values of any local variables inside the stored procedure when compiling the statement. Use this option when atypical or temporary values are used in only a subset of queries belonging to the stored procedure. For more information, see Query Hint (Transact-SQL).

3 ) You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

Examples are given below for recompilation.

1. CREATE PROCEDURE EmployeeData

(

@EmpName varchar(50)

)
WITH RECOMPILE
AS
SELECT * FROM Employee WHERE EmployeeName = @EmpName

2. EXEC  dbo.EmployeeData  ‘Rahul’  WITH RECOMPILE

Pawan Kumar

Pawankkmr@hotmail.com