0

We have a SQL Server 2016 SP1 CU7 Enterprise server where we enabled the query store on a database. This is a highly used database.
The Max Size has been set to 200MB and Stale Query Threshold is 1 day.
I've used sys.database_query_store_options to check the properties of the query store and everything works perfect as long as the current_storage_size_mb stays under the 200MB.
I've set Size Based Cleanup Mode to Auto but no cleanup happens, normally a cleanup should be triggered when the query store is 90% full. What happens next is that the query store goes into readonly mode because there is no available space left.
I've tried changing Query Store Capture Mode to Auto but nothings changes.
If I then change Stale Query Threshold to 30 days the actual_state_desc changes to READ_WRITE and the query store starts capturing again. The current_storage_size_mb just becomes higher than the max_storage_size_mb and after a while the actual_state_desc goes back to READ_ONLY.

Can anybody explain this behavior?

1

A few days ago I updated the server to SQL Server 2016 SP2 and the problem disappeared. Now when the querystore hits 90% it is cleaned up till it reaches 80%.

I don't know if SP2 was the solution or was it the restart of the SQL Service due to the update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.