Recompiles and Constant Learning

May 11, 2010 at 3:31 pm (SQLServerPedia Syndication, TSQL) (, , )


When faced with a procedure that looks like this:

CREATE PROCEDURE dbo.TestProc (@TestValue INT)
AS
BEGIN
IF @TestValue = 1
BEGIN
SELECT *
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @TestValue
END
ELSE
BEGIN
SELECT *
FROM Production.Product AS p
JOIN Production.ProductDocument AS pd
ON p.ProductID = pd.ProductID
WHERE p.ProductID = @TestValue
END
END

I used to suggest creating a wrapper procedure in order to avoid the recompiles that occur when the different paths through the IF statement are taken by the optimizer. I mentioned that recently on a post over at SQL Server Central. Gail Shaw (blog | twitter) asked me why I thought there would be a recompile. She said that the optimizer took the query as a whole and created plans for it. I never seem to learn my lesson, so I suggested that she might be wrong about that. Gail being who she is, immediately went and made up a quick little test with simple queries. Sure enough, no recompiles. Ah, but I figured she was benefiting from trivial plans or something, so I created the procedure above to test the theory out. Each query, while relatively simple, goes through a full optimization process, so no trivial plans involved…

Short answer, Gail’s right and I was wrong. I don’t know where I got the idea that this type of query caused recompiles. I have now tested it on 2000, 2005 and 2008, no recompiles anywhere. I also cleared the cache, ran the procedure once, and then checked the cache using this query:

DBCC freeproccache ;

EXEC dbo.TestProc

@TestValue = 1 ;

SELECT deqp.query_plan

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp

This is what I saw:

 

I swear, I’d never seen a plan like this before. I guess partly because I tend to only look at the actual execution plan rather than the estimated plan. It’s pretty clear that the optimizer just walked through and determined that there was more than one query involved and built a plan for them, including the conditional IF statement. If I’d just bothered to look at the estimated plan one time, I could have avoided my error of understanding.

To all those I’ve suggested wrapper procs in order to avoid recompiles… oops, sorry.

10 Comments

  1. Gail said,

    Wrapper procs are often a good idea though. Not because of the recompiles though, because of the lack thereof. http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/

  2. Roy Ernest said,

    Well, I too thought that it could cause problems if you have different queries being executed based on an IF condition. This is what I thought, since the stored proc can have multiple plans based on the path the query takes, it could cause in getting a bad query plan when getting executed.

  3. scarydba said,

    Believe me, I’m not dismissing the idea of a wrapper proc. If nothing else, it can act to reduce complexity and increase readibility. But I really did think it affected recompiles. I do love learning new stuff.

  4. Kev Riley said,

    So the different paths don’t cause a recompile, but can suffer from bad cached plans?
    However they would benefit from a recompile!

  5. scarydba said,

    Yeah, they can suffer from bad cached plans, just like anything else. With statement recompiles now, you don’t need to sweat the isssue that one will cause problems for the other.

  6. Gail said,

    Why not?

  7. scarydba said,

    Well, that said, yes, it can. While that statement is recompiling, other processes will be waiting…. but now I’m thinking. My initial thought was, with a statement recompile, you wouldn’t need to worry about a different plan for the other statement, but, is that completely true? If these are linked through the IF clause, does that make them a single statement?

  8. Gail said,

    Ah, I see what you’re saying. A recompile of one statement won’t cause problems for someone running the other branch.

    Maybe…. Needs testing. I remember some nasty cache contention problems on SQL 2000, where connections had to wait for the compile to complete before they could run, but don’t know how 2005’s statement-level changed that.

  9. RBarryYoung said,

    Heh. A person could lose a lot of money betting against Gail on stuff like this … :-)

  10. Gail said,

    Especially since I never take a bet unless I know I’m going to win.

    Some developers at my previous company learnt that the hard way, and I got free lunch on a number of occasions.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: