Oct 05 2009

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

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.

15 Comments

  • By John Sansom, October 5, 2009 @ 7:43 am

    Great article, thanks for sharing.

    I have a question if I may:

    I can see and understand your validation that a stored procedure also produces an “SP:CacheMiss” event and then a “SP:CacheHit” event however, can you explain why this course of events occurs?

    So to clarify, why does a miss occur prior to a hit even when the plan is cached?

  • By Phil Factor, October 5, 2009 @ 7:50 am

    Interesting post, Grant.

    Count me in with the people who give, and believe, the ‘story’ that ‘stored procedures get and reuse execution plans, but ad hoc queries do not’ and who ‘say as much online and in the real world’. Of course, if you reckon that a parameterized query is ‘ad hoc’ then that certainly complicates the debate.

    The way I’ve always looked at it, an Ad-Hoc query ceases to be an ad-hoc query once it has become a parameterized query. Once it has become a parameterized query, I’ve always found them to work as if they were stored procedures: to all intents and purposes, it is, surely, a temporary stored procedure.

    This discussion hinges on your understanding of the term ‘Ad-Hoc query’, of course, but I’m not sure I accept that ‘There are ad hoc queries and there are ad hoc queries’. I therefore reckon it is a bit confusing to use the word ‘myth’ when referring to the fact that ad-hoc queries cannot re-use query plans if a parameter is changed. Heaven knows, the ORM brigade find that the whole subject of plan-reuse makes their heads swim, and I suggest that we have to keep the message clear and simple, even if it makes for a fascinating discussions for anyone doing database development work.

  • By scarydba, October 5, 2009 @ 8:48 am

    All true Phil. I guess I’ve always thought of ad hoc as built queries, either on the client or on the server. Seperated from pre-built (not pre-compiled of course) queries which are procedures, etc. From both sides, client & server, they can be built in a silly fashion that completely precludes plan reuse or they can be built properly so that they take advantage of code reuse. I guess it is primarily a definition of terms situation.

  • By scarydba, October 5, 2009 @ 9:00 am

    Just to check, I went and looked at several sources… You’re right & I’m wrong on this one Phil. Most people are very precise in differentiating between ad hoc queries and prepared queries (Kalen Delaney’s term).

  • By Jeremiah Peschka, October 5, 2009 @ 9:46 am

    Following on what Phil said, I typically refer to the prepared queries as “Dynamic SQL”… the other types of query are “Ad Hoc SQL” and “Stored Procedures”. It helps explain things. They also show up differently when you’re looking at the plan cache since ad hoc queries show up as ‘ad hoc’… I forget what the others appear as, but they’re different as well.

  • By scarydba, October 5, 2009 @ 10:40 am

    John, sorry I left you hanging there. My understanding of why you see a miss is that the batch itself goes through a cache check and finds a miss, but the procedure finds a hit. If you ran the same query again, without changing the parameter value, I think you’d see two hits, but I’d have to test that again to be sure.

  • By Gail, October 5, 2009 @ 11:24 am

    Even the first example of ad-hoc query can have it’s plan reused if SQL decides itself to parameterise it. Either because it’s a very simple query and qualifies for auto-parameterisation or because Force Parameterisation is set on.

  • By Denis Gobo, October 5, 2009 @ 12:16 pm

    You can also check sys.dm_exec_query_plan and sys.dm_exec_sql_text and check usecounts to see if you het a new plan or if it gets reused…a little easier instead of using profiler

    examples here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/changing-exec-to-sp_executesql-doesn-t-p

  • By scarydba, October 5, 2009 @ 1:29 pm

    Gail,

    Yes, absolutely. I left it out of the discussion though since it’s pretty difficult to count on simple/forced parameterization for most queries. The original ad hoc query didn’t result in simple parameterization due to the JOIN. Take away the JOIN and you do get a parameterized query.

    Denis,

    Yes, that’ll work, but if you want to see the events as they occur rather than try to track down if they’ve occurred, trace events are the way to go.

  • By Sam Trenchard, October 7, 2009 @ 11:26 am

    But why the Cache miss followed by Cache hit rather than Cache hit for each subsequent call ?

  • By scarydba, October 7, 2009 @ 12:28 pm

    Because the batch as well as the procedure get a cache check. When you change the parameter value, it’s a new batch and gets a miss.

  • By forex signals service, February 28, 2013 @ 8:05 am

    I have been exploring for a little bit for any high
    quality articles or blog posts in this sort of space
    . Exploring in Yahoo I eventually stumbled upon this website.
    Reading this info So i am satisfied to exhibit that I have an incredibly excellent uncanny feeling I came upon exactly what I needed.
    I so much without a doubt will make sure to
    don?t forget this site and provides it a glance regularly.

Other Links to this Post

  1. Log Buffer #165: a Carnival of the Vanities for DBAs | Pythian Group Blog — October 9, 2009 @ 11:55 am

  2. Log Buffer #165: a Carnival of the Vanities for DBAs « PlanetMysql.ru – информация о СУБД MySQL — October 9, 2009 @ 2:11 pm

  3. My Weekly Bookmarks for October 9th | Brent Ozar - SQL Server DBA — October 9, 2009 @ 10:33 pm

RSS feed for comments on this post. TrackBack URI

Leave a comment