Hi Friends,

Today here, I am explaining the Use of Recompile Clause in SQL Server Stored Procedures.

We use stored procedures in sql server to get the benefit of reusability. Some time, we also use WITH RECOMPILE option in stored procedures. Here i am focusing on why we use WITH RECOMPILE option.

When we execute stored procedure then sql server create an execution plan for that procedure and stored that plan in procedure cache. If we again execute the same procedure then before creating a new execution plan sql server search that plan in procedure cache. If plan found in cache then it reuse that plan that means we save our CPU cycles to generate a new plan. But sometimes plans generation depends on parameter values of stored procedures. In this case if we reuse the same plan for different values of parameters then performance may degrade.

For Example, create a table xtdetails and create indexes on them and insert some data as shown below:

Now, I am inserting the data into this table:

Here table xtdetails contains 10000 rows, where only 10 rows having name = asheesh and address=Moradabad.

Now create stored procedure as shown below:

Now execute this stored procedure as:

The output of this execution generates below mention statistics and Execution plan:

1_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

2_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

Now executing the same procedure with different parameter value:

The output of this execution generates below mention statistics and Execution plan:

3_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

4_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

Here when we execute stored procedure again it uses the same execution plan with clustered index which is stored in procedure cache, while we know that if it uses non clustered index to retrieve the data here then performance will be fast. Now again creating that stored procedure with RECOMPILE option.

Now execute this stored procedure as:

The output of this execution generates below mention statistics and Execution plan:

5_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

6_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

Now execute this stored procedure as:

The output of this execution generates below mention statistics and Execution plan:

7_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

8_SQL_Server_Use_of_Recompile_Clause_in_Stored_Procedures

Here you see the better execution plan and great improvement in Statistics IO.

This is because of the WITH RECOMPILE option, here each execution of stored procedure generates a new execution plan. We should use RECOMPILE option only when the cost of generating a new execution plan is much less then the performance improvement which we got by using RECOMPILE option.

In last do not forget to off the statistics IO, as shown below:

 

Regards

Prince Rastogi

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook