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.
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.)