How to Tell if Execution Plans are Reused

October 4, 2010 at 8:00 am (SQL Server 2005, SQL Server 2008, SQLServerPedia Syndication, TSQL) (, , , , , )

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 a number of ways you can see if a plan is being reused. One of the easiest is to hit the DMOs that look into the plan cache. You can look at either sys.dm_exec_procedure_stats or sys.dm_exec_query_stats. These DMOs are somewhat different and somewhat the same. Actually, they sort of cross each other. For sys.dm_exec_procedure_stats, you get aggregate information about stored procedures. For sys.dm_exec_query_stats you get aggregate information about queries, which, may be run within stored procedures. So either or both could be useful depending on what you’re attempting to measure. However, both return a very useful counter, execution_count. Quite simply, that tells you that the plan, whether for the statement or the procedure, is being reused. To get maximum gain out using either of these, you’ll want to combine them with other DMOs. Something along these lines can show you the plans for procedures that have been running against a system, ordered by the number of times they’ve been reused:

SELECT deps.execution_count ,
OBJECT_NAME(deps.object_id, deps.database_id) 'Procedure' ,
deqp.query_plan
FROM sys.dm_exec_procedure_stats AS deps
CROSS APPLY sys.dm_exec_query_plan(deps.plan_handle) AS deqp
ORDER BY deps.execution_count DESC

The only thing wrong with using the DMO like this is that you can only see what’s currently in cache. This means no history, depending on how volatile the cache is on your system.

Another way to tell if a plan is being reused, is to set up a server side trace and capture the event for the SP:CacheMiss or SP:CacheHit. This is pretty straight forward. If the query was not in, it generates a miss event. If it was there, it generates a hit. But, this has to be running in order for you to know if you had a hit or a miss. The good news is, if it’s running, you’ve got historical information since this captures the event as it occurs.

You can also catch recompile events using SQL:StmtRecompile. Yes, you can get SP:Recompile if you’re only ever dealing with procedures, but if you’ve got any kind of ad-hoc querying going on in the system or triggers, you’ll miss recompile events. Basically, because recompiles are at the statement level starting in 2005, BOL recommends only using SQL:StmtRcompile.

That’s pretty much it. Using these methods in the appropriate place will let you know if the plan is being reused or not.

Permalink 1 Comment

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

October 5, 2009 at 7:00 am (SQL Server 2005, SQL Server 2008, TSQL) (, , , , )

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 hoc TSQL goes, that one is actually some what clean. They get a lot worse. But, in this case, each and every time the @value variable is changed, you’re going to get a different execution plan. That’s because the full text of the query is used to determine if the existing plan will work for the new query and changing the @value variable will result in a change to that text.

However, there is another way to build and execute ad hoc TSQL. Done correctly, it will work exactly the same way as stored procedures, including plan reuse. This method is known as a parameterized query. There ways to do this through OLEDB & ODBC and there’s a way to do it through TSQL, sp_executesql. Instead of the query above, let’s examine this query:

DECLARE @sql NVARCHAR(MAX), @value INT, @parm NVARCHAR(MAX);
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 = @internalvalue'
SET @parm = '@internalvalue int'
EXEC sp_executesql @sql, @parm, @internalvalue = @value;

Using sp_executesql you can define parameters and put them into the query. Now, when this query is called again, you’ll see that the execution plan gets reused. As part of the comparison, I’ll also create this procedure:

CREATE PROCEDURE dbo.AdHocTest
(@value INT)
AS
SELECT * 
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.SalesOrderDetail AS sod
ON soh.SalesOrderID = sod.SalesOrderID
WHERE soh.SalesOrderID = @value;

To test these queries, we’ll need to capture some trace events. Here are the ones I’ll capture:

  • SQL:BatchCompleted
  • SP:CacheMiss
  • SP:CacheHit
  • SP:CacheInsert

When all three queries are run, changing the parameter passed to @value, here is the resulting trace event output:

adhoc_trace

From the top, I ran each query in order as listed here in the article, changing the value passed once each time I called query. You first see a cache miss for the pure ad hoc query. It inserts to the cache and finishes the batch. Then, the next ad hoc query has a cache miss, a cache insert, and completes it’s batch. Proving that ad hoc queries with only a small change will restult in multiple execution plans.

Then we move on to the sp_executesql queries. They start out the same way, with a cache miss and an insert before the batch is completed. Note, the cache insert is for a different set of code than is immediately visible based on what we’ve provided. The TextData column starts with “(@internalvalue int)…” and procedes to show the rest of the query. This is the use of sp_executesql at work. It’s creating a parameterized query, effectively the same thing as a stored procedure. Then, with the second call to sp_executesql, we see a cache miss followed by a cache hit. This is the classic pattern for a successful call to the plan cache.

If you don’t trust that, the last two batches are the stored procedure. You can see that the first call to the procedure results in the same cache miss followed by a cache insert that every other first call had. The second call to the stored procedure had a cache miss followed by a cache hit. The same as the second, ad hoc sql, call to sp_executesql.

That, my friends, is plan reuse in action from an ad hoc query. Myth 1/2 Busted. Or maybe: Myth “It Depends” Busted.

Permalink 14 Comments