3

I'm running SQL Server 2016 and I'm getting this error:

There is insufficient system memory in resource pool 'default'

...when running batch queries with millions of rows.

I decreased the batch size from 1000 to 100 and I still get this error. I have shown what my current settings are at for my SQL Server.

enter image description here

enter image description here

I didn't change any of these settings. I have the SQL Server installed on my 2TB hard drive.

I can share the code but basically I'm posting inserts for around 3 million rows into a table in my database. I can't even make one of my tables memory optimized because it gives me the same error when I try.

Everything I have checked makes it look like it has plenty of memory so I'm not sure what to do next. This only seems to happen when I'm doing batch queries on my memory optimized tables.

I tried the command ALTER RESOURCE GOVERNOR RESET STATISTICS as suggested in SQL2014EE 'Insufficient System Memory' message by Henk VanderValk. I thought it worked at first, but it hasn't worked on memory optimized tables, though it seemed to work on regular tables. I'm almost positive this has to do with the fact that I'm using mostly memory optimized tables but I don't know what to try.

UPDATE: I followed the steps at https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/bind-a-database-with-memory-optimized-tables-to-a-resource-pool and now I get the same error message but with the name of my pool instead. I also have the full details for my pool below but I'm not sure how to change the max memory that the pool is pulling from

enter image description here

  • Do you actually have that much memory on your system? Have you considered setting the max memory to be more representative of what you have installed? SQL Server fighting with the OS for memory leads to bad things happening, which could be a part of the trouble. – Nic Aug 11 '17 at 19:13
  • How exactly are you doing the transition to your memory optimized table? Are you staging them in normal tables, and then shooting them into the memory optimized ones? I'm asking because you say the link I provided works for "normal" tables. – Nestor Aug 12 '17 at 11:58
  • @Nestor I did a test and tried the transactions in a normal table and it seemed to work without any issue then I changed that table to a memory optimized table and that is when I started getting the error. I also updated my post to show what I have done so far – user3610374 Aug 12 '17 at 12:10
  • Questions: 1. Are you INSERTing directly to the memory-optimized tables? 2. @Nic is correct, we need to know how much physical memory is on your system? 3. Running physical or virtual? 4. Are you committing after each batch, or do you have one big transaction? – NedOtter Aug 19 '17 at 4:19
  • this article might help: sqlserverlearner.com/2011/… – CR241 Aug 28 '18 at 22:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.