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).

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.

Comments

Posted by Tom Fischer on 7 October 2009

Nice to see such a regularly repeated question answered so clearly. Thanks!

Posted by Suba Thiruvasagam on 8 October 2009

Clear and to the point. Nice.

Posted by Grant Fritchey on 8 October 2009

Glad you found it useful. Of course, I kind of cheated by treating parameterized queries as ad hoc. Most people differentiate between the two.

Posted by Anantharaman on 8 October 2009

It is true that latest RDBMS are smart enough to optimize ad-hoc SQLs in the same way as Stored Procs. So "Performance" is no longer an excuse for using Stored Procs over ad-hoc SQLs.

However , ad-hoc SQLs need to make there way from client application to database system , thereby making network payload bulkier in case of very verbose SQL statements. Moreover ad-hoc SQLs can easily expose the underlying table schema.

It is for this "Schema Abstraction" that DBAs still prefer SPs for accessing data as there is no necessity to expose table schemas -- just gateways (SPs) for updating & fetching information as a resulset will be sufficient.

Posted by Amit Pandey DeBugSQL on 9 September 2012

Grant, but how about analyzing a work load.

After running a SQL Trace, I can easily identify what procs are the top consumers. How will I group the adhoc sql or parameterized sql for that matter since the text will keep changing.

Example:

sp_adhoc called 500 times avg duration 200 MSec.

I feel that adhoc will be difficult to analyze.

We can ask the business to increase the resources but after a certain level, we have to fix the queries which are top consumers, at the moment I find it difficult to ananlyze the adhoc queries since they are too many in numbers. I put a trace recently and I got 800K adhoc queries had been called whose duration was over 500 Mseconds. How will I be able to say the following are the top 10 big consumers.

Posted by Amit Pandey DeBugSQL on 9 September 2012

Grant, but how about analyzing a work load.

After running a SQL Trace, I can easily identify what procs are the top consumers. How will I group the adhoc sql or parameterized sql for that matter since the text will keep changing.

Example:

sp_adhoc called 500 times avg duration 200 MSec.

I feel that adhoc will be difficult to analyze.

We can ask the business to increase the resources but after a certain level, we have to fix the queries which are top consumers, at the moment I find it difficult to ananlyze the adhoc queries since they are too many in numbers. I put a trace recently and I got 800K adhoc queries had been called whose duration was over 500 Mseconds. How will I be able to say the following are the top 10 big consumers.

Posted by Amit Pandey DeBugSQL on 9 September 2012

Grant, but how about analyzing a work load.

After running a SQL Trace, I can easily identify what procs are the top consumers. How will I group the adhoc sql or parameterized sql for that matter since the text will keep changing.

Example:

sp_adhoc called 500 times avg duration 200 MSec.

I feel that adhoc will be difficult to analyze.

We can ask the business to increase the resources but after a certain level, we have to fix the queries which are top consumers, at the moment I find it difficult to ananlyze the adhoc queries since they are too many in numbers. I put a trace recently and I got 800K adhoc queries had been called whose duration was over 500 Mseconds. How will I be able to say the following are the top 10 big consumers.

Leave a Comment

Please register or log in to leave a comment.