Load into memory-optimized table gives insufficient memory error I am trying to load a memory-optimized table with rows from a disk-based table. It is just a simple INSERT..SELECT since the table structures are almost the same.

The database is on a dedicated server (not a VM) with 512 Gb RAM. However, I keep on hitting the following error:


  Msg 701, Level 17, State 103, Line 10
  
  There is insufficient system memory in resource pool 'default' to run this query.


The table has about 66 million rows and about 50 columns. Max server memory is set to the default 2147483647 Mb. Max memory % on the resource pool is set to 100%. The server is running SQL server 2016 SP1.

Things that I've tried so far, with no success:

• Enclosed the INSERT..SELECT in a loop so that it will only insert 10,000 rows at a time. It will run for some iterations and then error out. The number of iterations varies a lot at each run -- it could error out after 10,000 one time, and then go for a million the next.

• Created a dedicated resource pool for the database, instead of using the default (read this somewhere). Same problem.

• Tried using SQL server 2017 RC2. Same problem.

Any help would be greatly appreciated.

Added:

The INSERT..SELECT looks like the following:

INSERT INTO apc.Policy
    (PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, LastUpdateDate)
SELECT 
    PolicyID, ReportingCode, CarrierCode, -- and some more columns
    ProcessDate, GETDATE()
FROM Policy (NOLOCK)
WHERE PolicyID BETWEEN @Index * 10000 + 1 AND (@Index + 1) * 10000 -- @Index is the loop index
ORDER BY PolicyID


(This was originally posted in StackOverflow. Closed there. Opened here instead.)
Setting max memory to 2147483647MB is a really bad idea, as it could/would destabilize the operating system.

You've posted your SQL server is the following version:


  Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30


I would strongly recommend patching this server to CU4, because they contain many bug fixes that are specific to In-Memory.

If you are still having problems:


Is compression used for the disk-based table? 
After creating a resource pool, and binding the database to it, did you OFFLINE/ONLINE the database to make the binding effective?