SQL Server 2017 freezing during bulk insert of large file into in-memory staging table

Question

Running SQL Server 2017 CU20 Standard on a dedicated Dell PowerEdge with 28 cores and 192GB of RAM.

I have a stored procedure that processes large files (~7GB each) in a loop by bulk inserting them one by one into a memory-optimized staging table (durability = schema only). Normally, everything works great. I even have an email alert that monitors the memory used by the data and indices to see if anything is growing out of control (more than 5 GB). I have never seen the total go above 10 GB because the staging table is dropped and recreated each iteration to free up memory.

Every few weeks, the database will just totally hang and be inaccessible. I can't even RDP into the machine to reboot it and I end up having to force a reboot via out-of-band management.

I thought it was because CU25 might have had unknown issues, so I rolled back to CU20 which has been completely stable on another server. That did not fix the problem unfortunately.

What could the issue be? What logs can I check to see what's going wrong?

EDIT: I checked the event viewer and I am indeed getting "There is insufficient system memory in resource pool 'internal' to run this query."

How could this be happening? Is there a maximum size for memory-optimized tables that I am bumping up against? When I re-run the job after rebooting, it completes without any issues.

Answer 1

This link should answer your question:

http://nedotter.com/archive/2018/01/in-memory-oltp-resources-part-4-oom-the-most-feared-acronym-in-all-of-in-memory-oltp/

There is no max memory size for memory-optimized tables, but that doesn't mean you can't "run out of memory", based on the Edition you're running.

Bottom line is that if you are running Standard, the amount of memory allocated to In-Memory structures is a moving target, in a sense.

You've not mentioned it, but I'm wondering if there is also a traditional-table workload running concurrently. Also wondering if rows are being deleted from any memory-optimized tables while you are loading data. This would be relevant for garbage collection.