3

I'm working on updated procedures that I inherited from someone who is no longer at the company.

I have found procedures that include WITH RECOMPILE option after the header. In the notes it says that it was added "to mitigate timeouts from blocking"

ALTER PROC ups_SomeProc (@pMyParameter INT) WITH RECOMPILE
AS
BEGIN

In all my experience I never heard of WITH RECOMPILE option helping with blocking or even be related to blocking in any way.

Am I missing some understanding about this option or the person who implemented it was the one who got confused on what it does? Have anyone heard of this before as a solution for blocking?

Note: This was done when server was still running SQL Sever 2008 edition.

  • Is this sproc called very frequently? The only thing I can think of is if the recompile forces the sproc to run less frequently reducing contention, but I wouldn't think it would affect it very much. – D Stanley Mar 03 '14 at 21:25
  • My understanding was that it was used for avoiding Parameter Sniffing just like [it is described here by a Microsoft Engineer](http://blogs.msdn.com/b/turgays/archive/2013/09/10/parameter-sniffing-problem-and-workarounds.aspx). In regards how often it is run I'm not sure, currently working on implementing tracking of the procedures to get some more insight into it. –  Mar 03 '14 at 21:32

2 Answers2

2

I can think of one way it could help (which I actually experienced myself). Each stored proc has a query plan cached. There could be a problem sometimes if the way the stored is executed varies wildly based on some values as the cached query plan may be completely unsuitable.

Let's say you have a stored proc that looks like

create procedure SomeProc 
as
begin
    declare @value int

    select @value = somevalue from atable where date = getdate() -- getting different value every time

    if @value = 1 then -- do one thing
    if @value = 2 then -- do something different
    if @value = 3 then -- do something completely different
end

The query plan might have been cached when the value was 1. When you run it again and the value is now 2 or 3, the information is not suitable and may result in a query execution that takes very long time. You can sometimes spot such queries by having wildly varied number of reads, etc.

When you use WITH RECOMPILE, it can mitigate those problems by forcing SQL Server to come up with a new execution plan.

Szymon
  • 42,577
  • 16
  • 96
  • 114
  • That is the **Parameter Sniffing** which was already avoided by use of local variables. But I do agree that it case of parameter sniffing it would be able to help. –  Mar 03 '14 at 21:35
  • 1
    You can still have problems with an unsuitable execution plan even if you don't have parameters. In my example above, what you execute varies but is not based on parameters. Usually, you would have `IF`s in a stored proc. – Szymon Mar 03 '14 at 21:39
2

OPTION WITH RECOMPILE forces Sql Server to Recompile an execution plan even if there is an existing plan cached in the memory.

If the underlying data changes dramaticly and very quickly the cached execution plan becomes less efficient. Therefore in such situation using WITH RECOMPILE option executes the procedure much faster then it would execute if it had used the already compiled execution plan.

My guess the developer/person experienced some long delays when he executed the stored procedure. and when he used the WITH RECOMPILE option it got executed faster. So maybe he thought executing this procedure without recompile option causes blocking. :) funny story but I think this is what happened.

M.Ali
  • 67,945
  • 13
  • 101
  • 127