Sql-server – In-Memory OLTP SQL Server 2016 – insufficient system memory

memory-optimized-tablessql serversql-server-2016

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

Here is the memory estimate you will need:

enter image description hereenter image description here

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

enter image description here

The size of my table is approximately 22GB

enter image description here

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

enter image description here

enter image description here

enter image description here

Can anybody help me?
Thanks.

Best Answer

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.

Related Question