In-Memory OLTP SQL Server 2016 - insufficient system memory

Question

I am trying to place an In-Memory OLTP table,

Here is the memory estimate you will need:

But when the migration starts, the following error message is displayed:

The size of my table is approximately 22GB

I have a server with 64GB of memory and I allocated it for SQL 56GB, and to put the table in memory I only need 23GB

Can anybody help me? Thanks.

Answer 1

So my guess is you may not actually have enough Memory for that table to be used as an In-Memory Table.

As mentioned in my comments, per this Brent Ozar article Hekaton (In-Memory OLTP) Tables In Use, it sounds like one of the requirements are you have at least twice as much Memory available as is the size of your In-Memory Tables:

But you need to understand the limitations, and they’ve changed a lot over the years...

Your memory needs to be 2x the size of your data

While you dedicated 56 GB of Memory to your SQL Server instance, a 23 GB table to be moved to an In-Memory Table means you should have at least 46 GB available for just that table, following Brent Ozar's advice. And because SQL Server uses the Memory you allocate to it for caching other things like data pages loaded in Memory, the 10 GB you have left over from 56 GB - 46 GB is not a lot to rely on. I.e. I wouldn't doubt your instance is already consuming more than 10 GB and therefor you don't have 46 GB available to provide to your In-Memory Table.

This article Estimate Memory Requirements for Memory-Optimized Tables in Microsoft's Books Online also is a good guide on how to estimate how much Memory you'll actually need.

I would recommend testing a much smaller table that's only a few GB or so as a proof of concept. Then work your way up. If you get that working, then you can try increasing your Memory a bit further before retrying your original table.

Answer 2

Have you bound the database to a specific resource pool?

Try to increase the memory available to the pool.

Bind a Database with Memory-Optimized Tables to a Resource Pool - Percent of memory available for memory-optimized tables and indexes

If you map a database with memory-optimized tables and a SQL Server workload to the same resource pool, the Resource Governor sets an internal threshold for In-Memory OLTP use so that the users of the pool do not have conflicts over pool usage. Generally speaking, the threshold for In-Memory OLTP use is about 80% of the pool. The following table shows actual thresholds for various memory sizes.

When you create a dedicated resource pool for the In-Memory OLTP database, you need to estimate how much physical memory you need for the in-memory tables after accounting for row versions and data growth. Once you estimate the memory needed, you create a resource pool with a percent of the commit target memory for SQL Instance as reflected by column 'committed_target_kb' in the DMV sys.dm_os_sys_info. For example, you can create a resource pool P1 with 40% of the total memory available to the instance. Out of this 40%, the In-Memory OLTP engine gets a smaller percent to store In-Memory OLTP data. This is done to make sure In-Memory OLTP does not consume all the memory from this pool. This value of the smaller percent depends upon the Target committed Memory. The following table describes memory available to In-Memory OLTP database in a resource pool (named or default) before an OOM error is raised.

Evaluate it with this query

USE master 
GO

;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