Query Store and Automated Cleanup

Query Store has mechanisms for automatically cleaning your data. It is possible to cause them to break down. While presenting a session about the Query Store recently, I was asked what happened if you set the size of the Query Store below the amount of data currently in the store. I didn’t know the answer, so we tried it. Things got a little weird.

Bryan Hundley of Marathon Consulting asked the question, so Bryan, this blog post is for you.

Automated Cleanup

There are actually two kinds of automated cleanup inside the Query Store. First, you have a time-based cleanup. By default it keeps queries that have been accessed within the last 30 days. Anything older, it tosses. If you have the stale_query_threshold_days (all in sys.database_query_store_options) set to 0, it disables the time-based cleanup. You can adjust this value up if you like, you’ll just need more disk space.

Query Store also has a size-based cleanup (there was a bug on this in 2016, it was fixed a long while back in CU 1, make sure your servers are up to date). As you add data to the Query Store, if it hits 90% of the max_storage_size_db, it will drop older queries and less expensive queries from the Query Store, their plans, runtime statistics and wait statistics, until it reduces to 80% of the max_storage_size_db. You can turn this off by changing size_based_cleanup_mode to 1 (2 is the default and means it’s running on automatic).

Between these two settings, you should be able to easily, and automatically, control what’s in your Query Store information without running into trouble.

Running Into Trouble

So, what did I do? At the time of the demonstration, I had about 5mb of storage in my Query Store. Bryan asked what happened when I set it to below that threshold. So, I did. Suddenly, I didn’t have any data. I set it back to 100mb, poked around a little, and everything was fine. I didn’t have a good explanation for exactly what was going on.

After some experimentation, I can tell you what happened.

When I changed the storage to 1mb, the automated cleanup kicked in. It removed queries in an attempt to get to 80% of the storage. That’s why I suddenly couldn’t see some of the queries we’d been monitoring all night. However, it was also completely full, so it actually changed the status of the Query Store from “On” to “Read Only”. You can see the results of this by running a query:

SELECT dqso.current_storage_size_mb,
       dqso.max_storage_size_mb,
       dqso.size_based_cleanup_mode_desc,
       dqso.actual_state_desc,
       dqso.desired_state_desc,
       dqso.readonly_reason
FROM sys.database_query_store_options AS dqso;

The results look like this:

Follow the link above to sys.database_query_store_options to find the readonly_reason value interpretations. You’ll see that 65563 means that I ran out of room.

So, while the query store did attempt to clean up data for me (quite successfully), by setting the max_storage_size_mb to such a silly low number, the Query Store moved itself to read only mode in order to deal.

I can easily recover with the following statements:

ALTER DATABASE AdventureWorks2017
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 100);

ALTER DATABASE AdventureWorks2017
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

ALTER DATABASE AdventureWorks2017
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 100);

ALTER DATABASE AdventureWorks2017
SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

The most important takeaway here is that, I lost all my Query Store data by setting the value so low.

Conclusion

Maybe demos aren’t the right time to try things. However, it did show off how Query Store behaved when it ran out of space. I just hadn’t done anything like it to be able to offer a full explanation at the time. Now, I can explain exactly how it works when it runs out of space in this fashion. Thanks Bryan!

If you’d like an opportunity to ask me a question that breaks my demo, I’d like to give you the opportunity. I’ll be presenting an all day seminar on tools for query tuning, including the Query Store, at the following events (with one more to come):

For SQLSaturday Philadelphia on April 20, 2018. Please sign up here.

For SQLSaturday NYC on May 18, 2018. Go here to register.