How to fix Query Store statement hash map has reached memory limit

Question

I am running

Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
Sep 23 2020 16:03:08
Copyright (C) 2019 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: ) (Hypervis

I have Query Store enabled but it is showing as "Read_Only" even though I have it set to Read_Write. When I try to view regressed queries, etc. I see an error:

"Query Store statement has map has reached memory limit". I have searched but found no articles on resolving this issue.

I increased the "Max Storage to 4gb, from 1 gb. I clicked on "Purge Query Data", I disabled and re-enabled the query store and I still get the same error.

Yes, all my data files have plenty of space in them. yes the files are still in the primary file group.

Data Flush Interval - 15 minutes
Statistics Collection Interval - 1 day
Max Plans Per Query - 200
Query Store Capture Mode - Auto
Size Based Cleanup Mode - Auto
Stale Query Threshold - 90
Wait Statistics Capture Mode - On

What do I do to resolve this?

Answer 1

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.

Answer 2

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

Answer 3

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.)

Answer 4

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.