question

TimShirey-5871 avatar image
0 Votes"
TimShirey-5871 asked ·

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

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?

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered ·

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/

· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I don't think this is a regular memory issue for QS. It seems to be related to hashing the queries and that hash map. Just as an observation...

0 Votes 0 ·

The response from Tibor below is correct. This is not a regular memory issue, the error is that the "hash map" is full. The query store has been working on my instance since last October and has only now had an issue.

How do I fix an issue with the "Hash Map"?

As you can see in my question description, I have already taken the steps outlined in "Best Practice with the Query Store" before I posed this question. The Read_Only error code I am getting is "131072".

If you still think this is a memory issue, then how do I resolve this? None of the articles you listed above have had any effect.

0 Votes 0 ·
TiborKaraszi avatar image
0 Votes"
TiborKaraszi answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks Tibor. I posted and voted to that forum. I still need a way to get my Query Store working and I have not seen an article that has been helpful.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Unfortunately that has no effect.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ·

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.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, I have opened a support case. I just tried here to to exhaust all my options.

0 Votes 0 ·