Removing ad hoc plans from Query Store

This is not a post about the “optimize for ad hoc workloads” setting on your favorite SQL Server instance, but that is a good place to start.

Lots of folks that I’m too busy/tired/lazy to link to have promoted using this setting as a best practice for keeping your plan cache from bloating up like Violet Beauregarde in the Chocolate Factory. The theory goes, if a query is just run once, why do you even care about keeping the execution plan? Just bin it.

That said, this IS a post about Query Store, that nifty tool in the newer versions of SQL Server. It’s got a lot of functionality, such as persisting nearly all plans, which includes those ad hoc queries whose plans you probably didn’t want in the first place.

Yeah, unfortunately, there’s nothing like “optimize for ad hoc workloads” for Query Store. But for those of you who don’t want Query Store drowning in all your ad hoc nonsense, let me share a little something.

Deep Ocean, Vast Sea

Now, rather than being my usual rambling self I want to be very direct here: this solution will NOT give you the same behavior as “optimize for ad hoc workloads.” That setting keeps query info without the plan during the first execution, but then keeps the plan after the second execution.

That’s kinda like a surgeon with a scalpel. What is below is much more drastic. We’re going to break out a chainsaw for Query Store.

The thinking is this: Query Store is largely used for queries you want to tune or maybe even force a plan for, right? Well, the workloads for many databases we support at my employer are defined largely by two types of queries: stored procedures used by some application, and ad hoc queries run by users. Realistically, we can only tune the former. The latter, well maybe they could try an Itzik Ben-Gan class.

Given this, we decided having those ad hoc queries in Query Store was a kind of noise. So in order to reduce the noise, we wrote a stored procedure to eliminate the ad hocs.

Roll Call

(Disclaimer: I keep saying “we” because this whole undertaking was largely a collaboration between myself and my colleague LeAnne Jergensen. We each took turns ripping apart updating each other’s code.)

The tricky thing was: how do we determine an ad hoc query? We tried linking sys.query_store_query back to sys.dm_exec_query_plan, but we couldn’t get that to work. Both DMVs have query plans, but those plans aren’t identified the same way, using different IDs. Plus one is persisted and the other isn’t, and for a bunch of other reasons we went to Plan B.

Plan B: any query in sys.query_store_query without an object_id (object_id = 0) we’re gonna call ad hoc. Problem solved!

Bonus: Eventually we also decided to exclude internal queries (is_internal_query = 1), since we’re not looking to tune those either.

What we ended up with includes a table variable, dynamic T-SQL, and sp_MSforeachdb. Oh, and an explicit transaction for speed.

Stop laughing. I mean, I was tempted to slap some Delayed Durability in there as well. Yeah, it could probably be made a little more elegant, but it’s fast enough right now.

Cuts You Up

CREATE PROCEDURE [dbo].[usp_PurgeAdHocFromQueryStore]
AS
SET NOCOUNT ON
 
DECLARE @command varchar(1000)
 
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ?

EXEC(''
DECLARE @idList TABLE (query_id INT PRIMARY KEY CLUSTERED);
DECLARE @query_id int;


INSERT @idList(query_id)
SELECT query_id
FROM sys.query_store_query
WHERE is_internal_query = 1
OR object_id = 0;
 
BEGIN TRAN;
 
WHILE EXISTS (SELECT query_id FROM @idList) BEGIN
      
       SELECT @query_id = (SELECT TOP 1 query_id FROM @idList ORDER BY query_id);
 
       EXEC sp_query_store_remove_query @query_id;
 
       DELETE FROM @idList WHERE query_id = @query_id;
 
       END;
 
COMMIT;
'') END' ;
 
EXEC sp_MSforeachdb @command;

As you can see, this will run against ALL user databases. You should modify that NOT IN to an IN list if you want to be specific.

We also created a job that runs intermittently to reduce the impact of executing the procedure, sometimes as frequently as every 5-10 minutes. That’s something you’ll have to tune for your own server.

One last thing: if you want to keep some amount of ad hoc queries, such as 24 hours worth of executions, here is a little more Query Store guidance from Microsoft. Check the section “Delete ad-hoc queries,” which has a much bigger query but was helpful to us when we started trying to figure out how remove these.

Hope this helps keep your Query Stores at a manageable size!