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
05Oct 2009 by Grant Fritchey 15 Comments

Ad Hoc Queries Don’t Reuse Execution Plans: Myth or Fact

SQL Server, T-SQL
Another frequently heard story is that stored procedures get and reuse execution plans, but ad hoc queries do not. A lot of people believe this and say as much online and in the real world. Unlike my last myth, this time, I'm going to give you the DBA answer to this question. It depends. There are ad hoc queries and there are ad hoc queries. The classic ad hoc query looks like this: DECLARE @sql NVARCHAR(MAX), @value int; SET @value = 43668; SET @sql = 'SELECT * FROM Sales.SalesOrderHeader AS soh '; SET @sql = @sql + 'JOIN Sales.SalesOrderDetail AS sod '; SET @sql = @sql + 'ON soh.SalesOrderID = sod.SalesOrderID '; SET @sql = @sql + 'WHERE soh.SalesOrderID = ' + CAST(@value AS NVARCHAR); EXEC (@sql); And as ad…
Read More