Handle memory pressure alerts from memory-optimized tables in SQL Server
by Rackspace Technology Staff
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.
Recent Posts
Google Cloud Hybrid Networking Patterns — Part 1
October 17th, 2024
Google Cloud Hybrid Networking Patterns — Part 3
October 17th, 2024
Google Cloud Hybrid Networking Patterns — Part 2
October 17th, 2024
How Rackspace Leverages AWS Systems Manager
October 9th, 2024
Windows Server preventing time sync with Rackspace NTP
October 7th, 2024