SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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,
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

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!

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.

The post Query Store and Automated Cleanup appeared first on Grant Fritchey.

The Scary DBA

I have twenty+ years experience in IT. That time was spent in technical support, development and database administration. I work forRed Gate Software as a Product Evangelist. I write articles for publication at SQL Server Central, Simple-Talk, PASS Book Reviews and SQL Server Standard. I have published two books, ”Understanding SQL Server Execution Plans” and “SQL Server 2008 Query Performance Tuning Distilled.” I’m one of the founding officers of the Southern New England SQL Server Users Group and its current president. I also work on part-time, short-term, off-site consulting contracts. In 2009 and 2010 I was awarded as a Microsoft SQL Server MVP. In the past I’ve been called rough, intimidating and scary. To which I usually reply, “Good.” You can contact me through grant -at- scarydba dot kom (unobfuscate as necessary).


Leave a comment on the original post [www.scarydba.com, opens in a new window]

Loading comments...