What is the use of with recompile statement?
Click to see full answer
Consequently, what is the use of recompile in SQL Server?
RECOMPILE – specifies that after the query is executed, its query execution plan stored in cache is removed from cache. When the same query is executed again, there will be no existing plan in cache, so the query will have to be recompiled.
Likewise, what is with recompile option in SP? 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.
Secondly, why do we need to recompile stored procedures?
Another reason to force a stored procedure to recompile is to counteract, when necessary, the "parameter sniffing" behavior of stored procedure compilation. When SQL Server executes stored procedures, any parameter values used by the procedure when it compiles are included as part of generating the query plan.
How do you recompile a procedure?
To recompile a stored procedure by using sp_recompile Select New Query, then copy and paste the following example into the query window and click Execute. This does not execute the procedure but it does mark the procedure to be recompiled so that its query plan is updated the next time that the procedure is executed.