Query Store Data Cleanup - Grant Fritchey (2024)

Microsoft supplies quite a few knobs to control how Query Store performs data cleanup. You can set your cleanup various ways, and, they interact. Let’s talk about how and why Query Store does it’s data cleanup.

Query Store Size Limit

The most important thing to understand here is that Query Store won’t just keep collecting data forever, filling your hard drive. There is a hard limit to how much data Query Store contains. By default, prior to 2019, that was 100mb. After 2019, it’s 1,000mb. You can, of course, adjust this up, or down, as needed on your systems. It’s a database-by-database setting (as so much of Query Store is). You can change this through SSMS:

Change the “Max Size (MB)” value. That’s it. Nice & simple. You can also control it through T-SQL:

ALTER DATABASE CURRENT SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 2000);

Let’s assume you’ve disabled data cleanup in all ways. When it hits the max storage size, Query Store will switch to read only mode and stop collecting data. If you don’t have monitoring in place, checking the status, you won’t know about this, so it’s something to keep in mind.

Size-Based Data Cleanup

I like the way Erin puts it, this is one you should never hit. Size your storage appropriately so that you can keep the amount of Query Store data that you want. Then, rely on the time-based cleanup (talk about it below) to fix your data. However, if you need to, and as a safety mechanism, I recommend it, you can set a size-based cleanup. When you get to 90% of the max value, Query Store will purge data. This is a rather indescriminate purge too, so you won’t have a lot of history on your queries when it’s done. Of course, forced plans, stuff like that, won’t get purged.

To change this value in T-SQL:

ALTER DATABASE CURRENT SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

If you look up above, you can see the setting in SSMS.

Time-Based Data Cleanup

I would argue the preferred mechanism for a data cleanup in Query Store is to use the time-based model. You just have to tell Query Store how many days worth of data you want to store. The default is 30 (except for some Azure SQL Databases). That may be too much, or too little, for you. You can adjust it in SSMS, as shown above. Or, you can set it through T-SQL:

ALTER DATABASE CURRENTSET QUERY_STORE ( CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 15) );

Now, when the stale query threshold is exceeded, the old data, queries that haven’t been run for 15 days (in my example) and runtime data about queries that is older than 15 days, will both be removed. When the query is removed, so, of course, is the execution plan. And, as before, forced plans, forced hints and the like will be left alone.

The interaction between time-based and sized-based data cleanup is already established. Assuming you have them both on, Query Store will clean up the data based on whichever threshold it hits first. If it’s time-based, you’ll only be losing a day of data at a time, and, further, you won’t be risking Query Store going into read only mode. If it’s size-based, it may or may not clean up the data in time to avoid going to read only mode. Yeah, Microsoft cautions that depending on how fast your data is growing, if you hit the size threshold, you get read only mode, at least temporarily (scroll down to the size-based cleanup section). Further, as was already mentioned, you can’t control what data is kept.

With all this in mind, time-based with an appropriate size limit and the size-based as a backup is the right path forward.

Manual Data Cleanup

Just so you know, you can skip both these approaches and just do the data cleanup yourself. This is not what I recommend, but I want you to know it’s possible.

First, we can nuke the Query Store data. In SSMS it’s just a button:

Where it says “Purge Query Data” is the BOOM button. You can also do this through T-SQL:

ALTER DATABASE CURRENT SET QUERY_STORE CLEAR;

That does the BOOM.

You can also target specific queries to remove them:

EXEC sys.sp_query_store_remove_query @query_id = 42;

That will remove the query as well as the execution plan and any runtime metrics.

Conclusion

So that’s how you can cleanup data within Query Store. The automated mechanisms are certainly preferred, but you can do it manually if you choose. Hopefully this makes the relationship between time-based and size-based data cleanup a little more clear.

Related