Handle memory pressure alerts from memory-optimized tables in SQL Server

Introduction

The Microsoft SQL Server is very smart in terms of memory management, but sometimes memory pressure alerts and database engines demand more memory, leading to errors. This post discusses how to resolve one of the exceptional scenarios you might
experience because of memory pressure on SQL Server®; 2019 (Enterprise Edition)® caused by the memory-optimized tables (In-memory Online transaction processing (OLTP)). The same steps apply to SQL Server 2014 and later.

You might see the following error messages flash on your screen:

    Message: MSSQL on Windows: Stolen Server Memory is too high
    Source: XXXXX\MSSQLSERVER Path: Not Present Alert
    description: SQL instance "MSSQLSERVER" Stolen Server Memory on
    computer "XXXXXXX.XXX.com" is too high.

    Message: SQL Server Alert System: 'Severity 17' occurred on \\XXXXXXX
    DESCRIPTION:   There is insufficient system memory in resource pool 'internal'
    to run this query.

    Message: Disallowing page allocations for database 'InMemoryDB' due to
    insufficient memory in the resource pool 'default'. See
    'http://go.microsoft.com/fwlink/?LinkId=510837' for more information.

    Message: XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 32
 

Solution

The first step is to check the memory consumption in the SQL buffer pool.

As you can see from the preceding image, the database in question, InMemoryDB, consumes only 0.017% of the buffer pool.

Step two

Then you check the OS Memory Clerks using the following T-SQL command:

                                        select * from sys.dm_os_memory_clerks order by pages_kb desc

The results show that the total of top consumers was around 80% of the totalmaximum server memory.The size of the memory-optimized tables is also less than 2 GB, which you can see in the preceding image for the name, **DB_ID_6**. As such, ideally, there should not be any memory pressure on the server.

Step three

After reviewing the Out of Memory (OOM) links mentioned in the error log,http://go.microsoft.com/fwlink/?LinkId=510837, you need to bind the databasewith memory-optimized tables to a resource pool. This binding is a best practicefor databases with memory-optimized tables. Follow the steps to create a resource pool in the resource governor and bind the database.

Best practices recommend that you protect SQL Server from having its resources consumed by one or more memory-optimized tables and prevent other memory users from consuming memory needed by memory-optimized tables. Therefore, you should
create a separate resource pool to manage memory consumption for the database with memory-optimized tables.

  Steps to bind to a resource pool

Step 1. Create Resource Pool with the Memory allocations:

   USE [master]
       GO

       CREATE RESOURCE POOL [Admin_Pool] WITH(min_cpu_percent=0, 
          max_cpu_percent=100, 
          min_memory_percent=15, 
          max_memory_percent=15, 
          cap_cpu_percent=100, 
          AFFINITY SCHEDULER = AUTO,
          min_iops_per_volume=0,
          max_iops_per_volume=0)
       GO
Note: To avoid out-of-memory conditions, the values for min_memory_percent and max_memory_percent should be the same. In this case, the memory-optimized tables are very small, with 15% of the total server memory allocated to the resource pool. Don't forget to use the links in the references to calculate the percent of memory in your environment.

In this case, the memory-optimized tables are very small, with 15% of the total server memory allocated to the resource pool. Don't forget to use the    links in the references to calculate the percent of memory in your environment.

Step 2: Verify the resource pool and bind the database to it:

  EXEC sp_xtp_bind_db_resource_pool 'InMemoryDB', 'Admin_Pool'  
       GO

3. Verify the bind in sys.databases:

       SELECT d.database_id, d.name, d.resource_pool_id  
       FROM sys.databases d
       GO

Step 4. Restart the database to make the binding active:

       ALTER DATABASE DB_Name SET OFFLINE  
       GO  
       ALTER DATABASE DB_Name SET ONLINE  
       GO   

Note: If the database is always on, perform the steps on both the nodes, and instead of Step 4 (restart databases), perform a database failover to thesecondary instance.

Conclusion

In this case, all alerts related to memory pressure stopped after adding the databases with memory-optimized tables to the resource pool. I monitored the SQL Server error logs for a couple of weeks for this particular issue, and there were no traces of any memory pressure. These steps helped fix the memory pressure onthe database engine level with minimal downtime.