Does "With recompile" recompile all the queries in stored procedure?

Question

So we have quite a large database and a stored procedure which searches through a large number of documents. Depending on the context, it either fetches millions of documents or just one hundred.

Point is, the procedure takes 30 seconds for both millions and hundred documents which is surreal. If I add OPTION (RECOMPILE) after each of the five queries, it takes a second for 100 documents and (expected) 30 seconds for millions of documents.

I've tried creating a procedure with WITH RECOMPILE option but it seems that it doesn't recompile queries in it.

Is this correct? Does the WITH RECOMPILE on stored procedure recompiles inner queries or just the execution plan for an entire SP? How can I do this without repeating OPTION (RECOMPILE) after each query?

Answer 1

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

Answer 2

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.