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:

Then, we just need an ad hoc query:

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,
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:

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:

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.

8 thoughts on “Query Store and Optimize For Ad Hoc

Please let me know what you think about this article or any questions:

This site uses Akismet to reduce spam. Learn how your comment data is processed.