{"id":7257,"date":"2021-07-15T12:43:04","date_gmt":"2021-07-15T12:43:04","guid":{"rendered":"https:\/\/rax-stg-wp-aws.com\/blogs\/handle-memory-pressure-alerts-from-memory-optimized-tables-in-sql-server\/"},"modified":"2026-11-03T19:28:20","modified_gmt":"2026-11-03T19:28:20","slug":"handle-memory-pressure-alerts-from-memory-optimized-tables-in-sql-server","status":"publish","type":"blogs","link":"https:\/\/www.rackspace.com\/en-gb\/blogs\/handle-memory-pressure-alerts-from-memory-optimized-tables-in-sql-server\/","title":{"rendered":"Handle memory pressure alerts from memory-optimized tables in SQL Server"},"content":{"rendered":"

Introduction<\/strong><\/p>\n

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
\nexperience because of memory pressure on SQL Server\u00ae; 2019 (Enterprise Edition)\u00ae caused by the memory-optimized tables (In-memory Online transaction processing (OLTP)). The same steps apply to SQL Server 2014 and later.<\/p>\n

You might see the following error messages flash on your screen:<\/p>\n

    Message: MSSQL on Windows: Stolen Server Memory is too high
\n    Source: XXXXX\\MSSQLSERVER Path: Not Present Alert
\n    description: SQL instance “MSSQLSERVER” Stolen Server Memory on
\n    computer “XXXXXXX.XXX.com” is too high.<\/em><\/p>\n

    Message: SQL Server Alert System: ‘Severity 17’ occurred on \\\\XXXXXXX
\n    DESCRIPTION:   There is insufficient system memory in resource pool ‘internal’
\n    to run this query.<\/em><\/p>\n

    Message: Disallowing page allocations for database ‘InMemoryDB’ due to
\n    insufficient memory in the resource pool ‘default’. See
\n    ‘http:\/\/go.microsoft.com\/fwlink\/?LinkId=510837’ for more information.<\/em><\/p>\n

    Message: XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 32<\/em>
\n <\/p>\n

Solution<\/strong><\/p>\n

The first step is to check the memory consumption in the SQL buffer pool.<\/strong><\/p>\n

\"memory<\/p>\n

As you can see from the preceding image, the database in question, InMemoryDB<\/strong>, consumes only 0.017% of the buffer pool.<\/p>\n

Step two<\/strong><\/p>\n

Then you check the OS Memory Clerks using the following T-SQL command:<\/p>\n

                                        select * from sys.dm_os_memory_clerks order by pages_kb desc<\/em><\/p>\n

\"memory<\/p>\n

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.<\/p>\n

Step three<\/strong><\/p>\n

After reviewing the Out of Memory (OOM) links mentioned in the error log,http:\/\/go.microsoft.com\/fwlink\/?LinkId=510837<\/strong>, 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.<\/p>\n

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
\ncreate a separate resource pool to manage memory consumption for the database with memory-optimized tables.<\/p>\n

  Steps to bind to a resource pool<\/strong><\/p>\n

Step 1. Create Resource Pool with the Memory allocations:<\/p>\n

   USE [master]
\n       GO<\/em><\/p>\n

       CREATE RESOURCE POOL [Admin_Pool] WITH(min_cpu_percent=0, 
\n          max_cpu_percent=100, 
\n          min_memory_percent=15, 
\n          max_memory_percent=15, 
\n          cap_cpu_percent=100, 
\n          AFFINITY SCHEDULER = AUTO,
\n          min_iops_per_volume=0,
\n          max_iops_per_volume=0)
\n       GO<\/em>
\nNote<\/strong>: To avoid out-of-memory conditions, the values for min_memory_percent<\/strong> and max_memory_percent<\/strong> 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.<\/p>\n

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.<\/p>\n

Step 2: <\/strong>Verify the resource pool and bind the database to it:<\/p>\n

  EXEC sp_xtp_bind_db_resource_pool ‘InMemoryDB’, ‘Admin_Pool’  
\n       GO<\/em><\/p>\n

\n3. <\/em>Verify the bind in sys.databases<\/strong>:<\/p>\n

       SELECT d.database_id, d.name, d.resource_pool_id  
\n       FROM sys.databases d
\n       GO<\/em><\/p>\n

\"sql<\/p>\n

Step 4<\/strong>. Restart the database to make the binding active:<\/p>\n

\n       ALTER DATABASE DB_Name SET OFFLINE  
\n       GO  
\n       ALTER DATABASE DB_Name SET ONLINE  
\n       GO   <\/em><\/p>\n

Note<\/strong>: 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.<\/p>\n

Conclusion<\/strong><\/p>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

Handle memory pressure alerts from memory-optimized tables in SQL Server<\/p>\n","protected":false},"author":2,"featured_media":7259,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","format":"standard","meta":{"content-type":"","nid":"26341","content_type":"blog","created_by":"10130","authored_on":"2023-12-05T15:07:07+00:00","field_article_industries":"","tag_category":"Technical","field_blog_author_text":"by Rackspace Technology Staff","seo_description":"Learn to handle memory pressure alerts caused by memory-optimized tables in SQL Server. Find solutions for exceptional scenarios in SQL Server 2019+.\r\n","field_featured_image":"","insights_image":"\/wp-content\/uploads\/2026\/blog_technical_images\/26341-insights_image-database-technical-blog-2-insights.jpg","field_partners":"","rxt_platforms":"","product":"Microsoft SQL","publisheddate":"2021-07-15T12:43:04","rxt_solutions":"Cloud Management & Operations, Cloud Optimization","opengraph_tag":"","x_tag":"","teaser_flag":"Technical Blog","seo_titile":"SQL Server Memory Pressure Alerts | Rackspace Technology","field_topics":"","field_slug":"handle-memory-pressure-alerts-from-memory-optimized-tables-in-sql-server","footnotes":""},"categories":[178],"tags":[204],"class_list":["post-7257","blogs","type-blogs","status-publish","format-standard","has-post-thumbnail","hentry","category-blog","tag-technical"],"_links":{"self":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7257","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs"}],"about":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/types\/blogs"}],"author":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/comments?post=7257"}],"version-history":[{"count":2,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7257\/revisions"}],"predecessor-version":[{"id":9275,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/blogs\/7257\/revisions\/9275"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media\/7259"}],"wp:attachment":[{"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/media?parent=7257"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/categories?post=7257"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.rackspace.com\/en-gb\/wp-json\/wp\/v2\/tags?post=7257"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}