Recompiles and Constant Learning

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 thoughts on “Recompiles and Constant Learning

  • 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.

  • scarydba

    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.

  • Kev Riley

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

  • scarydba

    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.

  • scarydba

    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?

  • 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.

  • 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.

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.