SQL Server memory issue

Question

SQL Server memory issue

Heisenberg 261

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

Heisenberg 261 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 121.4K Reputation points MVP Volunteer Moderator

2023-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 261 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 261 Reputation points

2023-12-02T09:04:28.4933333+00:00

resource group.
Heisenberg 261 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 121.4K 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 261 Reputation points

2023-12-13T17:32:41.6233333+00:00

hi @Erland Sommarskog above query does not return any rows.

2 answers

Your answer

Heisenberg 261 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 121.4K Reputation points MVP Volunteer Moderator

2023-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 261 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 261 Reputation points

2023-12-02T09:04:28.4933333+00:00

resource group.
Heisenberg 261 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 121.4K 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 261 Reputation points

2023-12-13T17:32:41.6233333+00:00

hi @Erland Sommarskog above query does not return any rows.

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

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 DESC

As you are approaching the out-of-memory condition, something may be climbing to the top.

Heisenberg 261 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 121.4K 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.

Answer 2

Javier Villegas 900 MVP

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

Share via

SQL Server memory issue

2 answers

Your answer