Over a million developers have joined DZone.

Query Store and Automated Cleanup

DZone's Guide to

Query Store and Automated Cleanup

Learn how Query Store behaves when it runs out of space. Spoiler alert: It's not pretty.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

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,
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 65,563 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 the only mode in order to deal.

I can easily recover with the following statements:

ALTER DATABASE AdventureWorks2017
ALTER DATABASE AdventureWorks2017
ALTER DATABASE AdventureWorks2017
ALTER DATABASE AdventureWorks2017

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


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!

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

big data ,query store ,cleanup ,storage

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}