Ned Otter explains how a memory-optimized database can run out of its most critical resource:
What can cause a memory-optimized database to run out of memory? It could be that resource consumption (memory) exceeded:
- the relevant percentage of committed_target_kb from the sys.dm_os_sys_info DMV (explained in a moment)
- MAX_MEMORY_PERCENT value of a Resource Pool that the database is bound to (if running Enterprise Edition and using Resource Governor)
or:
- garbage collection is not operational (the purpose of GC is to reclaim memory consumed by stale row versions)
- updates to memory-optimized table variables caused row versions to be created, and because GC does not operate on table variables, you ran out of memory (for table variables that have a very large amount of rows)
The only thing that can prevent GC from working is a long running transaction.
This is the final post of Ned’s resource issues miniseries and it’s been a good one.