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:

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:

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:

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:

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 Twitter |  Follow me on FaceBook