sql – Does With recompile recompile all the queries in stored procedure?

sql – Does With recompile recompile all the queries in stored procedure?

Does the WITH RECOMPILE on stored procedure recompiles inner queries or just the execution plan for an entire SP?

inner queries or just execution plan,i am not sure what does this mean ? With Recompile at Stored proc level,will cause recompilation every time the proc is executed and query is not saved to Cache

How can I do this without repeating OPTION (RECOMPILE) after each query?

create proc usp_test
with Recompile 
as
Begin
--code
End

Some More Details:
With Recompile will recompile a new plan for the entire stored proc ,everytime its run..

suppose ,you have below proc

create proc usp_test
as
Begin
select * from t1
 go
select * from t2
End

adding recompile on top of stored proc,will cause SQLServer to recompile all the batches in Stored proc

Instead of recompiling,the total proc,if you know for sure ,which batch is causing issues,you can add Option(Recompile) like below

 create proc usp_test
    as

    Begin

    select * from t1 option(recompile)

    select * from t2

    End

doing this,you are avoiding unnecessary recompilation of other batches

Both OPTION(RECOMPILE) and WITH RECOMPILE will give you execution plans based on the parameters used each time, but only OPTION (RECOMPILE) allows parameter embedding optimization where the parameters are replaced with literal constants when its parsing the queries. This can allow the optimizer to make simplifications to the query.

I would find out which query is actually causing the performance issue and use OPTION(RECOMPILE) on that.

sql – Does With recompile recompile all the queries in stored procedure?

Leave a Reply

Your email address will not be published.