Hi @Tim Shirey ,
> 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.