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.