Param Stale Query Threshold (Days) doesn't work on QueryStore, SQL Server 2019

Question

Help me to deal with one problem.

We have SQL Server (Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Microsoft Corporation Developer Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) ), on which the database with QueryStore ON is located.

Parameters QueryStore: Parameters QueryStore

For some reason param Stale Query Threshold (Days) doesn't work. It was 1 day, now it's increased to 3 day. With both values QueryStore is not clean. At the moment, with these params, there are still some requests even older than 30 days.

After service reboot, manual cleaning QueryStore and QueryStore turning off and on - QueryStore still isn't clean.

There are also other SQL Servers, with 2019 Developer Edition, which have databases with parameter Stale Query Threshold (Days) equal 30 days and on these databases cleaning is successful.

Maybe there are some limits, where this parameter can't be less than 30 days? Or something else? I 'm trying to solve this problem for a long time and i can't understand why on this particular database QueryStore cleaning doesn't work and even if parameter set to 3 days, QueryStore keep requests older than 3 days.

I try:

• Change param from 1 to 3 days.
• Services and server reboot
• Manual cleaning
• Turning off and on
• Update SQL Server to CU18 (it was CU9)
• Turn off Size Based Cleanup Mode

Answer 1

"Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries, expressed in days. By default, Query Store is configured to keep the data for 30 days, which might be unnecessarily long for your scenario."

Some queries in the query store are probably still being executed within those three days so the statistics of those queries are not deleted from the query store.

https://learn.microsoft.com/en-us/sql/relational-databases/performance/manage-the-query-store?view=sql-server-ver16&tabs=ssms

Answer 2

You don't mention that you've tried updating statistics.

We force the db statistics to update nightly during a low usage period.
If that doesn't work, please note it in your 'tried' list.

Bad/old db statistics can look the same as a stale query.

If the first part of your table looks different than most of the rest of your table, then updating the statistics won't help - as it doesn't look at your whole table when it builds the statistics. <-- This is an edge case and is the only time I'd recommend using query hints in your query (using query hints after 2019 is almost always a bad idea, but it might help in your case)