0

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.

1
  • What happens if you truncate instead of dropping? Sep 30 at 10:49
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.

1
  • I have updated the max memory setting on the server to be ~180GB. I am on standard edition, so I didn't think this would matter since it's capped at 128GB but I think something is clearly overflowing at some point.
    – eek
    Sep 30 at 23:12

Your Answer

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

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