04Oct 2010 by Grant Fritchey 1 Comment

How to Tell if Execution Plans are Reused

SQL Server, T-SQL
I try to watch the search phrases that point people to the blog because sometimes, you get a sense of what problems people are running into. The latest question or phrase I've seen a lot lately is along the lines of "how do you know if an execution plan is being reused." Since compiling an execution plan can be an extremely expensive operation, it's worth your time to understand how well a given plan is getting reused. If you've seen me present, I'll frequently talk about the application that had a query with an 86 table join. Recompiles on that thing were frequent and extremely costly. The only good news was, they were recompiles. If we weren't getting plan reuse it would have been an even worse system than it was. There are…
Read More
11May 2010 by Grant Fritchey 10 Comments

Recompiles and Constant Learning

T-SQL
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…
Read More