Query Store and Optimize For Ad Hoc

I love presenting sessions because you get so many interesting questions. For example, what happens with Optimize for Ad Hoc when Query Store is enabled? Great question. I didn’t have the answer, so, on to testing.

For those who don’t know, Optimize for Ad Hoc is a mechanism for dealing with lots and lots of ad hoc queries. When this is enabled, instead of storing an execution plan the first time a query is called, a plan stub, basically the identifying mechanisms, for the plan is stored in cache. This reduces the amount of space wasted in your cache. The second time the query is called, the plan is then stored in cache.

I’m going to set up Optimize for Ad Hoc and Query Store and, to clean the slate, I’ll remove everything from cache and clear out the Query Store, just in case:

EXEC sys.sp_configure
    N'show advanced options',
    N'1';
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure
    N'optimize for ad hoc workloads',
    N'1';
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure
    N'show advanced options',
    N'0';
RECONFIGURE WITH OVERRIDE;
GO
DBCC FREEPROCCACHE();
GO
USE AdventureWorks2014;
GO
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON;
GO
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR;
GO

Then, we just need an ad hoc query:

SELECT  p.Name,
        soh.OrderDate,
        sod.OrderQty
FROM    Sales.SalesOrderHeader AS soh
JOIN    Sales.SalesOrderDetail AS sod
        ON sod.SalesOrderID = soh.SalesOrderID
JOIN    Production.Product AS p
        ON p.ProductID = sod.ProductID
WHERE   p.Name = 'Road-750 Black, 48'
        AND sod.OrderQty > 10;

If I run this query one time in my cleaned up environment, I can check to see if Optimize For Ad Hoc is working by querying the cache:

SELECT dest.text,
deqs.execution_count,
deqp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp
WHERE dest.text LIKE ‘SELECT p.Name,%’;

The results look like this:

So, what’s in the Query Store. We’ll use this query:

SELECT  qsqt.query_sql_text,
        qsq.count_compiles,
        CAST(qsp.query_plan AS XML)
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
WHERE   qsqt.query_sql_text LIKE 'SELECT  p.Name,%';

The results look like this:

In short, the plan is stored in the query store, even though the plan isn’t stored in cache. Now, this has implications. I’m not saying they’re good and I’m not saying they’re bad, but there are implications. If you’re in a situation where you need to use Optimize For Ad Hoc to help manage your cache, now, you’re going to possibly see negative impacts on your Query Store since it’s going to capture all the plans that you avoided. There are mechanisms for managing Query Store behavior.

I’m going to modify my own Query Store to change the capture behavior from “All” to “Automatic.” This enables an internal filtering mechanism, defined by Microsoft, to eliminate some captures. When I reset everything and run the example ad hoc query one time, I get the plan stub in cache, but nothing in the query store (that I can see). I run the ad hoc query again and now I get a plan in the cache, but nothing in the query store. If I run the ad hoc query for a third time, there’s a counter somewhere (I haven’t found it yet) because I suddenly get a query in the Query Store.

For a bit more information, let’s modify the Query Store query to include some runtime stats:

SELECT  qsqt.query_sql_text,
        qsq.count_compiles,
        CAST(qsp.query_plan AS XML),
        qsrs.count_executions
FROM    sys.query_store_query AS qsq
JOIN    sys.query_store_query_text AS qsqt
        ON qsqt.query_text_id = qsq.query_text_id
JOIN    sys.query_store_plan AS qsp
        ON qsp.query_id = qsq.query_id
JOIN    sys.query_store_runtime_stats AS qsrs
        ON qsrs.plan_id = qsp.plan_id
WHERE   qsqt.query_sql_text LIKE 'SELECT  p.Name,%';

Now, I run this query and the one from above against the cache, I get the following information:

 

(you might have to click on that to make it bigger).

Here’s the interesting bit. The execution_count from cache, the top set of results, is 2, even though I ran the query three times. What happens in cache is that the plan stub is removed and the count is reset. The bottom set of results, from Query Store, shows only a single execution.

What does all this mean? Just that as we add additional behaviors to our systems, we have additional management worries. With the ability to modify the Query Store behavior, you won’t need to necessarily worry that you’re going to get hurt by your need to use Optimize for Ad Hoc.