question

Hi @TimShirey-5871,

I have Query Store enabled but it is showing as "Read_Only" even though I have it set to Read_Write.

Query Store will automatically switches to a READ_ONLY state when it reaches the max memory size configured for the Query Store, you can see read_only_reasons in sys.database_query_store_options.

 USE [QueryStoreDB];
 GO
    
 SELECT actual_state_desc, desired_state_desc, current_storage_size_mb,
  max_storage_size_mb, readonly_reason
 FROM sys.database_query_store_options;

Query Store statement has map has reached memory limit

The most recent CU for each major release that supports Query Store limits the amount of internal memory used by Query Store at both the database and instance level. This limitation prevents the Query Store memory overhead from growing beyond a limit relative to the total available server memory. Please refer to this document Cumulative Update 8 for SQL Server 2019.

The purpose of these memory limits is to prevent other performance issues such as high waits, memory pressure, and locking contention that could be exposed through the use of Query Store for extremely ad hoc workloads.

To switch the Query Store operations mode back to read-write, see Verify Query Store is Collecting Query Data Continuously section of Best Practice with the Query Store.

---

If the response is helpful, please click "Accept Answer" and upvote it, thank you.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

[3]: https://www.sqlskills.com/blogs/erin/query-store-performance-updated/

I don't have an answer, but I did (out of curiosity) google for below and found some comments. From what I understand, there's no good way to handle this now, except for restricting QS space so you don't end up with that many queries. There seems to be an open case for this, probably at uservoice, where one can vote to get a memory based limit for QS (again from what I read). I.e., this seem to match your case: https://feedback.azure.com/forums/217321-sql-database/suggestions/42117001-add-a-memory-limit-based-cleanup-process-to-quer

I have never encountered this situation, but what happens if you lower the Stale Query Threshold so that the purge will actually delete data from QS? 90 seems a tad high to me anyway. (But I guess that if you have stuff that runs only once a quarter that you may need it.)

I think your best option is to option a support case, if you have not already have done so. Support cases are not exactly cheap, but you will be refunded if it is deemed to be a bug. And at least the fact that you cannot recover from the condition, certainly seems wrong to me.

If possible, can you share the number of any support ticket? Either here or personally to my mail esquel@sommarskog.se.