Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).

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.


Comments

Posted by Anonymous on 11 May 2010

Pingback from  Twitter Trackbacks for                 SQL Server Central, Recompiles and Constant Learning - The Scary DBA         [sqlservercentral.com]        on Topsy.com

Posted by Seth Phelabaum on 12 May 2010

To all those that I've suggested wrapper procs to in order to avoid recompiles because the guy who wrote the book on execution plans said it was a good idea... oops, sorry.

;).

Posted by Grant Fritchey on 12 May 2010

Guilty. I'll go quietly.

Leave a Comment

Please register or log in to leave a comment.