I've read online that with recompile will recompile the whole procedure whereas Option(recompile) will only the recompile a specific statement that it is used on. If a certain statement in the procedure is recompiled, won't it affect other parts of the procedure because now the optimizer has better stats to make better decisions later on (assuming the option(recompile) produces better stats) and thus causing a complete recompile of the procedure? How exactly does with recompile differ from option(recompile)?
Add a comment
|
1 Answer
WITH RECOMPILE and OPTION (RECOMPILE)
When a parameter-sensitivity problem is encountered, a common piece of advice on forums and Q&A sites is to “use recompile” (assuming the other tuning options presented earlier are unsuitable). Unfortunately, that advice is often misinterpreted to mean adding WITH RECOMPILE option to the stored procedure.
Using WITH RECOMPILE effectively returns us to SQL Server 2000 behaviour, where the entire stored procedure is recompiled on every execution.
A better alternative, on SQL Server 2005 and later, is to use the OPTION (RECOMPILE) query hint on just the statement that suffers from the parameter-sniffing problem. This query hint results in a recompilation of the problematic statement only. Execution plans for other statements within the stored procedure are cached and reused as normal.
Using WITH RECOMPILE also means the compiled plan for the stored procedure is not cached. As a result, no performance information is maintained in DMVs such as sys.dm_exec_query_stats.
Using the query hint instead means that a compiled plan can be cached, and performance information is available in the DMVs (though it is limited to the most recent execution, for the affected statement only).
For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: Only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.
Parameter Sniffing, Embedding, and the RECOMPILE Options by Paul White