SQL Server Memory Issue | Need Help

Question

Hello Experts,

We need your help in one of our SQL Memory issue. There exists a critical application in our environment and it is newly migrated to Windows and SQL 2019 from SQL 2008 . Predominantly, 56 GB is extra added to new server to get optimal performance from new server.

After migration, unlike previous server ,this new server is reporting memory problem and SQL is going down at least 2 times in a week and top of it due to Infrastructure limitation, we cannot add more RAM into it.

At very first place, we are not able to identify, whether it is Bufferpool or MTL problem since there is nothing unusual in memory clerks from auto DBCC memorystatus output during the issue .

FYI, Here is the error:

Error: 701, Severity: 17, State: 123

There is insufficient system memory in resource pool 'default' to run this query

Actions done so far:

1.Unloaded Antivirus DLL's from SQL server

2. Increased Virtual memory
3. Added 10GB more to max memory (Changed Max memory -180 to 190 GB )

Apps team conformed, nothing is changed from code side and on top of it 56 GB more memory is added to SQL. Can you pls help us to provide right pointer in this case to get root cause of this issue ? Many thanks !

Regards,

Naren

Answer 1

Hi @Poosa, Narendar (Contractor)

There is insufficient system memory in resource pool 'default' to run this query

Many factors can cause insufficient memory. Such factors include operating system settings, physical memory availability, components that use memory inside SQL Server, and memory limits on the current workload. In most cases, the query that fails with an out of memory error isn't the cause of this error. Overall, the causes can be grouped into three categories:

Cause 1: External or OS memory pressure

Cause 2: Internal memory pressure, not coming from SQL Server

Cause 3: Internal memory pressure coming from SQL Server component(s)

Please refer to this doc for more details: Troubleshoot out of memory or low memory issues in SQL Server.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

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.