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