SQL Server Stored Procedures – Use of Recompile Clause

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:

CREATE TABLE [dbo].[xtDetails]
(
    [id] [int] NOT NULL,
    [name] [varchar](50) NULL,
    [address] [varchar](50) NULL
) ON [PRIMARY]
GO
create clustered index IX_xtdetails_id on xtdetails(id)
GO
create Nonclustered index IX_xtdetails_address on xtdetails(address)
GO

Now, I am inserting the data into this table:

declare @a as int
declare @name as varchar(50)
declare @address as varchar(50)
set @a=1
set @name='prince'
set @address='gurgaon'
while @a<=5000
begin
insert into xtdetails values(@a,@name,@address)
set @a=@a+1;
end
	 
GO
 
declare @a as int
declare @name as varchar(50)
declare @address as varchar(50)
set @a=5001
set @name='Asheesh'
set @address='moradabad'
while @a<=5010
begin
insert into xtdetails values(@a,@name,@address)
set @a=@a+1;
end
 
GO
 
declare @a as int
declare @name as varchar(50)
declare @address as varchar(50)
set @a=5011
set @name='prince'
set @address='gurgaon'
while @a<=10000
begin
insert into xtdetails values(@a,@name,@address)
set @a=@a+1;
end

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

Now create stored procedure as shown below:

create procedure xspdetails(@address as varchar(50))
as
begin
select address,name from xtdetails where address=@address
end
GO

Now execute this stored procedure as:

set statistics IO on
exec xspdetails gurgaon

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:

set statistics IO on
exec xspdetails moradabad

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.

drop procedure xspdetails
go
create procedure xspdetails(@address as varchar(50))
WITH RECOMPILE
as
begin
select address,name from xtdetails where address=@address
end

Now execute this stored procedure as:

set statistics IO on
exec xspdetails gurgaon

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:

set statistics IO on
exec xspdetails moradabad

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:

set statistics IO off

 

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

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Prince Rastogi

Prince Rastogi is working as Database Administrator at Elephant Insurance, Richmond. He is having more than 8 years of experience and worked in ERP Domain, Wealth Management Domain. Currently he is working in Insurance domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe. He is post graduate in Computer Science. Prince is ITIL certified professional. Prince likes to explore technical things for Database World and Writing Blogs. He is Technical Editor and Blogger at SQLServerGeeks.com. He is a regular speaker at DataPlatformDay events in Delhi NCR. He has also presented some in depth sessions about SQL Server in SQL Server Conferences in Bangalore.

View all posts by Prince Rastogi →

Leave a Reply

Your email address will not be published. Required fields are marked *