Timing of the Automatic Query Store Purge

Keeping things tidy

Keep the most relevant data in Query Store
Configure the Query Store to contain only the relevant data and it will run continuously providing great troubleshooting experience with a minimal impact on your regular workload.

It goes on to list three specific settings to help accomplish this:

QUERY_CAPTURE_MODE - set this to AUTO to filter queries that are unimportant based on internal heuristics related to execution count, and execution / compilation duration. The default value for SQL Server 2016 and 2017 is ALL but you can run this command to change it:

SIZE_BASED_CLEANUP_MODE - set this to AUTO to have the oldest, and least resource intensive, queries deleted automatically when the data stored in the Query Store starts to approach the configured MAX_STORAGE_SIZE_MB

So I thought I’d fire up those extended events and see what I could find out.

Not documented

Here are the handful of things I discovered about the time-based automated cleanup process in my testing:

The cleanup happens about every 24 hours, give or take a few seconds

This may seem obvious, since the setting is STALE_QUERY_THRESHOLD_DAYS, but it’s not explicitly called out anywhere I’ve seen

The initial cleanup is not quite as predictable. It appears to run anywhere from 15-30 minutes after the database comes online. This was true in all the scenarios I tested:

the Query Store being enabled for the first time in the database

The SQL Server service starting up after a reboot

A backup of a database with Query Store already enabled being restored

the Query Store being disabled and re-enabled in a database

The data that’s deleted is based on the calendar day that’s X days before the current day. In other words, it’s not based on a rolling 24 hours from when the cleanup runs

In the example from my DBA Stack Exchange answer, you can see that the purge running on October 3rd deleted all of the Query Store data for August 3rd (my system was configured to retain 60 days of history)

A bonus tidbit I learned related to the Extended Events mentioned above: they does not fire at all of there is no data to be purged. It would be nice if this were documented somewhere.

In conclusion

At this point you might be saying, “Josh, why does this matter?” Well, that’s a fair question, and it probably doesn’t. Although the delete queries that purge the data run in the user database, it seems unlikely in practice that it should interfere with your production workloads.

One exception to that rule is if you accumulate large amounts of query store data in a given day. When the cleanup process hits that date, you could have a pretty big burst of I/O, since the deletes are logged like any other query.