SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Do IF statements cause recompiles?

I heard this one over at SSC a while back. “Avoid IF statements in stored procedures as they result in recompiles”

Ok, it sounds reasonable, if the optimiser optimises for the execution path taken on first execution it’ll have to go back and optimise the other paths when they are executed.

But is that really how it works? Now that I’ve spend some time looking at how the cache monitoring trace events behave, it’s possible to find out using those.

Let’s start with a simple example



CREATE PROCEDURE TestingRecompiles (@SomeParam INT)
IF (@SomeParam = 1)
SELECT SomeDate FROM Tbl2;

Simple enough. First execution will be with the parameter value of 1. I’m going to use Profiler to see what’s happening. Events traced are SP:CacheInsert, T-SQL:StmtRecompile and the XML Plan for query compile, so I can see exactly what plan was generated. I’m using the ‘For Query Compile’ event so that I can catch the plan at optimisation time, not at execution time.


With that trace running, I’m going to run the proc once with a value of 1 for the parameter. Important to note is that there is no cached plan for the procedure at this point.


I have two plan for query compile events, one cache insert for the procedure and a batch completed. I want to focus on the two compile events.

The first one shows a table scan on tbl1. The second shows another table scan, on tbl2. So it appears that both branches of the IF statement went through the optimiser and had plans created for them.



Before jumping to any conclusions, I want to see what’s sitting in the plan cache for this procedure.



One plan for that procedure and the plan shows a conditional (the if) and query operators for both branches.

So far it looks like the plan that was generated on the first execution contains plans for all of the statements and is sufficient for any execution, but to be sure, let’s see what happens when I run the query again with a different parameter.


No query compile, no cache insert, no recompile statement. So in this case there was no recompile from the IF.

Before drawing any conclusions, I want to check a more complex procedure, as the plans here were Trivial. Maybe full optimisation will show a different result.

USE Adventureworks

CREATE PROCEDURE TestingRecompiles2 (@SomeParam INT)
IF (@SomeParam = 1)
SELECT p.[Title], p.[FirstName], p.[MiddleName], p.[LastName]
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = e.[BusinessEntityID]
INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]
SELECT s.[Name], a.[AddressLine1],a.[AddressLine2],a.[City],a.[PostalCode]
FROM [Sales].[Store] s
INNER JOIN [Person].[BusinessEntityAddress] bea ON bea.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [Person].[Address] a ON a.[AddressID] = bea.[AddressID]

These queries are complex enough to go through full optimisation, but the profiler trace shows the same as before


Two showplan events prior to the completion of the first execution, one for each branch of the IF, one cache insert event and no showplan events, no recompile events when the second execution (with a different parameter value) began. (I’ll leave examining the plan in cache as an exercise to the reader)

One more, just to be thorough.

CREATE PROCEDURE TestingRecompiles3

INSERT INTO tbl1 (ID) -- Pre-populate with 1 row so that stats-based recompiles don't confuse the issue.

EXEC TestingRecompiles3


EXEC TestingRecompiles3

Much the same as we saw in the earlier tests. This time there are three showplan events, one for the subquery in the IF and one for each branch. Again there is one CacheInsert for the entire procedure and no recompile events on the second execution

So what is actually happening?

When the optimiser receives a batch or object to generate execution plans for it does not execute any portion of that code. That’s not its job, that’s what the Query Execution engine is there for. The optimiser’s job is to optimise each statement within the procedure or batch(1), to generate execution plans that are good enough and, in most cases, are reusable.

When it comes across an IF statement, the optimiser is not going to execute the expression to see which branch will be taken on this execution(2). It’s going to optimise both branches because it has no idea which one will be executed on this or subsequent executions.

For each statement within the procedure, the optimiser generates the best plan it can find based on the parameter values for the current execution. This is not necessarily ideal as it can end up optimising queries for parameter values that they will never be called with. As can be imagined, this can generate some not-so-optimal execution plans. I wrote about this problem some time back, so I’m not going to touch on it again

So, in conclusion… Do IF statements cause recompiles? No. The optimiser processes all branches of a conditional no matter which branch will be taken during execution, even if the branch can never be executed. Use of IF statements can however result in some really awful plans and make you sincerely wish they had caused a recompile. Use with caution (or recompile hints) if plans can be radically different depending on parameter values on the first execution.

(1) There are cases where the optimiser won’t optimise a statement within a procedure during the initial optimisation and will leave the statement to be optimised later (called deferred compile), but this is not due to IF statements. The one thing that immediately comes to mind that does cause this behaviour is when the statement depends on an object that does not exist at the point that the first optimisation is done, such as when a table is created within the procedure and then used within the same procedure.

(2) As proof that the optimiser doesn’t evaluate conditional expressions, consider the following:

IF (42/0 = 137)
SELECT name, sys.objects.type_desc FROM sys.objects

If that is executed, it fails with a divide by zero error (as expected). Requesting the estimated execution plan succeeds and returns a valid execution plan and executing it with the profiler trace that I used above (adding in the user error event) shows the Showplan for query compile (as the select within the IF is optimised), then a cache insert for that plan, and then only is the divide by zero error thrown, just before the SQL:BatchCompleted event.


Posted by Steve Jones on 14 December 2010

Excellent analysis. I'd be curious to see what happened if you had a few nested IF statements and how the plans turned out.

Posted by rajn.knit07 on 15 December 2010

Excellent blog. Good analysis and reason given. Very helpfull for interview purpose.

Posted by Pradeep Singh on 15 December 2010

Another myth busted!! Thank you for sharing this.

Posted by Adam Belebczuk on 15 December 2010

Excellent blog post! :-)

I agree with Steve, I'd be curious to see how this compiles with multiple nested IFs. Also, I've never heard this myth for stored procedures, but I have heard it for functions. I wonder if the optimizer would treat the two the same...

Posted by Russell T. Gould, Ph.D. on 15 December 2010

Very, very informative.  One of the best short articles I've seen in a while.

Posted by jparker2 on 15 December 2010

Did you try disconnecting and reconnecting to SQL server between executions? How about executing on two differant connections with two differant accounts? I have seen some serious differances in how the works by using seperate users and closing the connections between executions.

Posted by PocketJoshua on 15 December 2010

It's nice to see an analysis of this with actual results.

The results are not surprising at all. However, there are other issues with using IF conditionals in stored procedures.  As so well mentioned the optimizer can do a poor job on occasion building a query plan.

For example: You have a sproc, which has an IF statement, that is performing very poorly but when the contents of the sproc are executed as a script the performance is just fine.  This is a good time to consider other solutions such as dynamic execution of the conditional branches or moving the code of the branches into separate stored procedures to be called by the conditional in the original sproc.

I am curious if anyone has:

1) A concrete means of demonstrating a poorly performing sproc with a conditional.

2) Other solutions for dealing with this problem than outlined above.

Posted by PaulHunter on 15 December 2010

I don't think you can call it a myth busted... yet.

Assume procedure:

create procedure TestProc3

  @id    int  out

,  @value varchar(50)


set @id = isnull(@id, 0)

update someTable

set    someColumn = @value

where  id = @id;

if @@rowcount = 0 and @@error = 0


   insert  someTable

       (   someColumn )


       (   @value );

   set @id = scope_identity();



I think you'll end up with recompiles because here you're dealing with different statements based on the ability to update a record versus inserting a record.  (this is kind of represents a poor-mans merge).

Posted by GilaMonster on 15 December 2010

PocketJoshua: I've done both and the blog post I did that in is linked from this article.

Paul: Did you test that?

Re the multiple nested IF, I'll do a follow-on another time and briefly examine that.

Posted by Jeff Moden on 16 December 2010

Nicely done, Gail.  Proof positive that one good test (or 3, in this case) is worth a thousand expert opinions. ;-)

Posted by Jeff Moden on 16 December 2010

Paul Hunter wrote... "I think you'll end up with recompiles because..."

Since Gail gave some pretty clear instructions on how to test such code, I have to ask, did you test your code?

Leave a Comment

Please register or log in to leave a comment.