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.