Hello
If your VM has 500 GB RAM and SQL Server Max Memory Setting is 2 TB then you have a problem there.
I will say you should reduce Max Server Memory to less than 500, maybe 420 GB
Regards
Javier
SQL Server memory issue
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 54%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
Heisenberg
266
Reputation points
hi all,
Im getting below error in my errorlog after every couple of weeks. Once this error comes, sql server services shutdown or goes in "admin only mode" then i end up restarting sql services.
9/20/23 5:40 spid232 Error: 701, Severity: 17, State: 130.
9/20/23 5:40 spid232 There is insufficient system memory in resource pool 'default' to run this query.
9/20/23 5:56 spid29s Error: 701, Severity: 17, State: 123. 9/20/23 5:56 spid29s There is insufficient system memory in resource pool 'internal' to run this query.
Process/System Counts Value
Available Physical Mem ory 2316800000
Available Virtual Memo ry 1.39738E+14
Available Paging File 6728937472
Working Set 1388601344
Percent of Committed M emory in WS 99
Page Faults 27480103
System physical memory high 0
System physical memory low 1
Process physical memor y low 0
Process virtual memory low 0
9/20/23 5:59 spid29s
Memory Manager KB
VM Reserved 974977260
VM Committed 8987560
Locked Pages Allocated 509567092
Large Pages Allocated 6383616
Emergency Memory 1024
Emergency Memory In Us e 32
Target Committed 525060344
Current Committed 518554656
Pages Allocated 436441400
Pages Reserved 0
Pages Free 18982560
Pages In Use 499571584
Page Alloc Potential -764256
NUMA Growth Phase 0
Last OOM Factor 1
Last OS Error 0
9/20/23 5:59 spid29s
SQL Server | Other
-
Heisenberg 266 Reputation points
2023-12-01T13:14:20.26+00:00 i have a default configuration for resource pool. looks like there might be some issue related to resource pool. Can someone help me troubleshoot this issue
-
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator2023-12-01T22:02:41.47+00:00 What are the specs from this machine. From what I can read out from the data, they seem to be very humble. Maybe it is time to get more memory?
-
Heisenberg 266 Reputation points
2023-12-02T09:03:47.5366667+00:00 hi @Erland Sommarskog this is 500G RAM machine with BPE set to 1.8TB file on a ephemeral drive. its AWS EC2 instance. SQL 2016 SP3-GDR. There is one issue i see is max server memory is set to 2tb. can that cause any out of memory error?
my resource group configuration is attached in the screenshot.
How can i find out that is it a query that is causing the issue for out of memory or is there a configuration setting that is not utilizing all of the memory on the server. Is there any script i can run every minute or so that can log the highest memory consumer at particular point in time?
-
-
Heisenberg 266 Reputation points
2023-12-02T09:14:52.1333333+00:00 also after querying dm_os_buffer_descriptors i get below buffer cache usage by db. (Does this include BPE as well?) because its going well beyond physical memory on the server.
-
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator
2023-12-02T10:11:42.34+00:00 this is 500G RAM machine. its AWS EC2 instance. SQL 2016 SP3-GDR
Enterprise or Standard Edition?
There is one issue i see is max server memory is set to 2tb. can that cause any out of memory error?
That's the default, which means that SQL Server manages memory as it sees fit. It is often preferred to set the limit to something like 85-90% of the available RAM, but that is to create breathing space to the rest of the machine. It would not have much effect to avoid your out-of-memory condition.
my resource group configuration is attached in the screenshot.
There was no screenshot, but if this is something that relates to AWS, it would not tell me much.
also after querying dm_os_buffer_descriptors i get below buffer cache usage by db. (Does this include BPE as well?) because its going well beyond physical memory on the server.
Yes, as a quick glance in the documentation would tell you, it does. And you are using BPE?
In any case, out-of-memory is usually not caused by queries against simple disk-based tables.
Before we start to think about more systematic troubleshooting, tell us, does any of these two queries return any rows:
SELECT * FROM sys.dm_exec_xml_handles(NULL) SELECT * FROM sys.dm_db_xtp_memory_consumers -
Heisenberg 266 Reputation points
2023-12-13T17:32:41.6233333+00:00 hi @Erland Sommarskog above query does not return any rows.
Sign in to comment
2 answers
Sort by: Most helpful
-
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator
2023-12-13T22:09:05.2866667+00:00 hi @Erland Sommarskog above query does not return any rows.
Good. This means that you are not using In-memory OLTP (which obviously can outrun your memory if you don't watch out), and you don't seem to have any leaks with OPENXML. (One of the few ways you as a user can introduce a memory leak in SQL Server.)
I would suggest that you set up a job that inserts the data of these two queries in a table for each query once a day:
SELECT cast(sysdatetime() as date), * FROM sys.dm_os_memory_clerks ORDER BY pages_kb DESC SELECT cast(sysdatetime() as date), * FROM sys.dm_os_memory_objects ORDER BY pages_in_bytes DESCAs you are approaching the out-of-memory condition, something may be climbing to the top.
-
Heisenberg 266 Reputation points
2023-12-14T23:11:30.6466667+00:00 Thank you @Erland Sommarskog appreciate your response. i was also checking our sql sentry counters (monitoring tool) which pulls data from dmv sys.dm_exec_query_memory_grants. which has column granted_memory_kb. if i sum up this column or any other memory related column for all the sessions that were running at the time when issue occurred. that sum doesnt even come close to the total amount of memory i am having on the server, summation number is at least half of total memory. So my question is where is the memory going? is there a system dmv that i can run like every 5 minute and log it to a table, which will tell me what all queries were running and consuming memory at that point in time. This way i can nail down which query or number of queries are causing this issue.
-
Erland Sommarskog 133.7K Reputation points MVP Volunteer Moderator
2023-12-15T13:51:20.78+00:00 sys.dm_exec_query_memory_grants gives you how much memory that currently running and waiting queries needs for their execution. And more precisely that is memory for sort and hash operation. If they need to read a 400 GB table from start to end, this is not reflected here. If you see queries with NULL in the grant_time column here, this means that you are getting short on memory and you have a problem.
is there a system dmv that i can run like every 5 minute and log it to a table, which will tell me what all queries were running and consuming memory at that point in time. This way i can nail down which query or number of queries are causing this issue.
That's exactly the DMVs that I gave you. Except that they don't give you information by query, but memory objects and clerks. It is not very likely that a single query, or even a set of currently executing queries, that is the cause. Rather, it sounds as something that builds up over time, and that's why I asked about XML handles.
I styled the queries to be executed once per day, but you can of course run them more often if you use sysdatetime() as-is.
Sign in to comment -