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 CURRENT SET 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.
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.
3 thoughts on “Query Store Data Cleanup”
Grant – thoughts on whether or not to enable teh Query Store on SQL Server 2016? We use 2016 because that’s what our accounting software vendor provides us, we don’t have a choice in the version. After reading your article I went to check teh settings on ours and it’s turned off. That said I read a piece on Brent Ozars site and he said that earlier versions of teh store had some major gotcha’s.
Short answer, enable it.
Long answer. Hoo boy. Yeah, I do recommend it for 2016. However, there are fewer knobs for controlling the behavior, especially around query capture. That’s radically better in 2019. So, yeah, I’d enable it, but I’d do some testing to ensure that it’s not causing performance issues. For most people, most of the time, it’s going to be perfectly fine. You may hit issues, so testing is good. I’d suggest looking at the capture modes and using Auto as opposed to all, but that’s your call. But yeah, I’d use in 2016, even for databases running in compatibility mode.
Further, when you want to change those databases out of compatibility mode, having query store in place means, when you swap over and some of the queries go wonky (probably because of the cardinality estimation engine), you can force the plan back to the old behavior. Query store is an upgrade tool.
But still, testing is your friend.
It’s excellent for 2016 but you need the newest builds to get the most benefit