4

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

Improve this question
7
  • What's the size of the disk-based table? You can right-click on the table and click Properties, Storage, and look at Data Size for starters, or use sp_BlitzIndex.
    – Brent Ozar
    Commented Sep 14, 2017 at 18:17
  • Data space is 6,044.961 Mb. Index space is 8,239.281 Mb.
    – BenS
    Commented Sep 14, 2017 at 19:51
  • This might sound crazy, but I'm wondering if the SELECT is the problem. If you run the SELECT by itself, but insert into a temp table instead of an in-memory OLTP table, does it complete successfully?
    – Brent Ozar
    Commented Sep 14, 2017 at 22:51
  • I have edited the post to include a sample of the INSERT..SELECT. I have not yet tried it on a temp table but I have tried it on another disk-based table and that was successful. Note however that the INSERT is successful for some iterations before erroring out.
    – BenS
    Commented Sep 15, 2017 at 12:48
  • Which edition are you using? I know there is a limit that you can use by database on Standard and Web editions.
    – Danilo Braga
    Commented Sep 15, 2017 at 19:50

1 Answer 1

Reset to default
1

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?

Improve this answer
11
  • No, compression is not used. I have now set the max server memory to 120 Gb and the error still occurs. Yes, I did take the database offline/online, also even tried restarting the database service.
    – BenS
    Commented Sep 14, 2017 at 19:51
  • So you adjusted the max memory, but did you also adjust the percent associated with the resource pool? Also, please post the result of SELECT @@version
    – NedOtter
    Commented Sep 15, 2017 at 21:09
  • I kept the resource pool at 90% max memory. Here's the SQL Server version: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ).
    – BenS
    Commented Sep 17, 2017 at 19:28
  • I would strongly recommend patching this server to CU4, because they contain many bug fixes that are specific to In-Memory.
    – NedOtter
    Commented Sep 18, 2017 at 12:27
  • 1
    @AgentFire - if using a RANGE index (non-HASH) with a memory-optimized table variable, memory consumption can be orders of magnitude higher. See my post here: nedotter.com/archive/2016/08/…
    – NedOtter
    Commented Jun 8, 2018 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.