Are you using In-Memory OLTP in SQL Server 2014 and noticing that the In-Memory objects are consuming more memory than expected? In this article, we will explore how to optimize memory usage for In-Memory OLTP in SQL Server.

When using In-Memory OLTP, it’s important to understand that both the In-Memory engine and the traditional SQL Server engine share the same resources. This means that there is a limit to the amount of memory available for both engines. To manage memory usage, SQL Server provides configuration options such as “Max Server Memory (MB)” and “Min Server Memory (MB)”. These options allow you to set a hard limit on the instance’s memory consumption and define a baseline of memory that SQL Server won’t give up to the operating system in case of memory pressure.

In SQL Server 2014, you can further optimize memory usage for In-Memory OLTP by creating resource pools and binding databases with Memory-Optimized objects to these pools. SQL Server sets a limit on the memory consumption of In-Memory objects based on the available resources in the underlying resource pool. Even if you create a dedicated pool for Memory-Optimized objects, SQL Server will establish a threshold for memory usage according to the instance’s target committed memory.

Here is a table that shows the percentage of memory you can use for Memory-Optimized objects based on the memory assigned to a given pool:

To calculate the maximum memory available for In-Memory objects in a specific pool, you can use the following query:

WITH cte AS (
  SELECT RP.pool_id,
         RP.Name,
         RP.min_memory_percent,
         RP.max_memory_percent,
         CAST(RP.max_memory_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS max_memory_gb,
         CAST(RP.used_memory_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS used_memory_gb,
         CAST(RP.target_memory_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS target_memory_gb,
         CAST(SI.committed_target_kb / 1024. / 1024. AS NUMERIC(12, 2)) AS committed_target_kb
  FROM sys.dm_resource_governor_resource_pools RP
  CROSS JOIN sys.dm_os_sys_info SI
)
SELECT c.pool_id,
       c.Name,
       c.min_memory_percent,
       c.max_memory_percent,
       c.max_memory_gb,
       c.used_memory_gb,
       c.target_memory_gb,
       CAST(c.committed_target_kb *
            CASE
              WHEN c.committed_target_kb <= 8 THEN 0.7
              WHEN c.committed_target_kb < 16 THEN 0.75
              WHEN c.committed_target_kb < 32 THEN 0.8
              WHEN c.committed_target_kb <= 96 THEN 0.85
              WHEN c.committed_target_kb > 96 THEN 0.9
            END * c.max_memory_percent / 100 AS NUMERIC(12, 2)) AS [Max_for_InMemory_Objects_gb]
FROM cte c;

To optimize memory usage for In-Memory OLTP, you can create a resource pool and bind your database with Memory-Optimized objects to this pool. Here is an example:

CREATE RESOURCE POOL [InMemoryObjects] WITH (MIN_MEMORY_PERCENT = 50, MAX_MEMORY_PERCENT = 50);
ALTER RESOURCE GOVERNOR RECONFIGURE;
EXEC sp_xtp_bind_db_resource_pool 'SampleDB', 'InMemoryObjects';

Remember to set your database offline and then back online for the binding to take effect:

ALTER DATABASE SampleDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SampleDB SET ONLINE;

If you need to change the memory reservation for Memory-Optimized objects, you can easily reconfigure the memory limits by altering the resource pool configuration or changing the “Max Server Memory (MB)” server parameter. Here are examples:

ALTER RESOURCE POOL [InMemoryObjects] WITH (MIN_MEMORY_PERCENT = 65, MAX_MEMORY_PERCENT = 65);
ALTER RESOURCE GOVERNOR RECONFIGURE;

EXEC sys.sp_configure 'max server memory (MB)', 10240;
RECONFIGURE WITH OVERRIDE;

To unbind a database from a resource pool, you can use the following command:

EXEC sp_xtp_unbind_db_resource_pool 'SampleDB';

Remember to set your database offline and then back online for the unbinding to take effect:

ALTER DATABASE SampleDB SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE SampleDB SET ONLINE;

By optimizing memory usage for In-Memory OLTP in SQL Server, you can ensure efficient utilization of resources and improve the performance of your applications. Take advantage of the resource pools and configuration options available in SQL Server to fine-tune memory allocation for your Memory-Optimized objects.