5

I know what With Recompile means, and I know that it is not suggested at most of the times due to performance issues, no dmv's, etc..

Sometimes there are cases that it may help, but OPTION(RECOMPILE) may help too.

So that, when it is preferable to use With Recompile over OPTION(RECOMPILE)?

Thanks.

5

I don't think it ever is (unless you are stuck in SQL Server 2000 and are looking for pity - don't worry, you'll have it). I would opt for statement-level OPTION (RECOMPILE) every time. I'll quote the relevant bit from Paul White's blog post, Parameter Sniffing, Embedding, and the RECOMPILE Options:

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.

You can read on in the post to learn about this optimization, or read the whole thing to learn more about parameter sniffing and the different ways you can change SQL Server's behavior.

  • 1
    Thanks. Wow, I have read all this article some hours ago :) But still wasn't sure, so I decided to ask here. One more nice thing is that some days ago I watched your video with Kevin Kline - AD-104-S - and you said: never use With Recompile because it compiles all the stored procedure and instead use OPTION(RECOMPILE), so that I came to my work and asked our SQL team leader why does we have some stored procedure with With Recompile (and he is very good DBA, he is very experienced and always learning), he wanted me to learn all the cases when recompilation occurs and after that he would answer. – Misha Zaslavsky Sep 5 '14 at 18:29
  • So that after I will learn this, he told that he would explain me why and that there is a good reason. But maybe he is wrong !? – Misha Zaslavsky Sep 5 '14 at 18:34
  • 2
    @Misha Don't know, you'll have to share with us what he says is "good reason"... – Aaron Bertrand Sep 5 '14 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.