Use of Recompile option while creating Stored procedure in SQL Server

In my last article we saw how we can create a stored procedure and what are the benefits of using stored procedures in SQL Server. Today we are going to see what are additional parameters available for creating stored procedures. Lets see what are they.

Stored procedure with recompile option:- below is the script for creating a stored procedure with recompile option, if we create a stored procedure with recompile option, each time when it will be executed, it will generate a fresh execution plan and will never store any execution plan in the cache.

So in that scenario, purpose of creating stored procedure will be defeated. As we know stored procedure is well known for storing Execution plan when they are executed first time and it uses the same execution plan when these are executed subsequently.

Create procedure Get_Emp_detail(@Eid int)

With Recompile

as

begin

            select * from Employee where Eid=@Eid

end

Query to check the execution plan stored in the database:Using below query we can check execution plan stored within a database for all stored procedures.

Lets execute stored procedure that we created using above query and see if a execution plan is created or not.

Execute Get_Emp_detail 102

select

            db_name(database_id) as database_name,

            object_name(object_id) as sp_name,

            cached_time,

            last_execution_time

from sys.dm_exec_procedure_stats

where database_id = db_id('ETL')

In the above screen shot we can see that(in the area encircled by red color), there is no entry for stored procedure that we created. Hence if we are using “with Recompile” option while creating a stored procedure SQL Server will not store the execution plan in the cache and it will create a fresh execution plan each time when it is executed. Let’s create the same stored procedure without recompile option and see the difference.

Drop procedure Get_Emp_detail

go

Create procedure Get_Emp_detail(@Eid int)

as

begin

            select * from Employee where Eid=@Eid

end

Lets execute the newly created stored procedure and see the difference.

In the result set we can see that as soon as we executed the stored procedure, SQL Server stored the execution plan in the cache for further use. Lets execute the same stored procedure and see if same execution plan is being used or SQL Server create the new execution plan.

In the result set, if you look at the cached_time column, cache time value is still same at the second time execution of the same stored procedure. I hope, I was able to make you understand about recompile option.