Stored Procedure and Re-Compiling

 

 

Each time when we call the stored procedure the SQL server does not create a new access plan to retrieve the information of the database. The query of the stored procedure is optimized when it is compiled.

If we make some changes in the table structure or create new indexes which may optimize the data retrieval, but already compiled store procedure may lose efficiency.   By re-compiling the stored procedure we may optimize the query.

So the re-compiling of stored procedure is important when we change any related objects in that is used by stored procedure to make new execution plan for it and optimize the performance.

In this article, I am trying to illustrate how we can recompile the stored procedure to optimize performance and set new execution plan for it.

There are three way to re-compile the store procedure.

1.    By sp_recompile [stored procedure name]

   

It is forcing the stored procedure to re-compile next time it is executed.

For example

 

EXEC sp_recompile [SP Name]

2.    WITH RECOMPILE option

Create a stored procedure specifying WITH RECOMPILE option. If WITH RECOMPILE is specified 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. Ideally, we should not use this option because by using this option, you lose most of the advantages you get by substituting SQL queries with the stored procedures. However, we can use this option during early development stages of the project where database structure is still evolving and stored procedures should be recompiled as a result of these changes. 

 

      CREATE PROCEDURE usp_MyProcedure

      WITH RECOMPILE

      AS

      SELECT SampleName,

             SampleDesc

      FROM   SampleTable

GO

 

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.

 

For example:

 

 EXEC usp_MyProcedure WITH RECOMPILE

 

 

 

Posted By: MR. JOYDEEP DAS