My database has several memory optimized tables and procedures that use the memory optimized table (non-native compiled procedures). We are getting an error in our development system indicating lack of memory resource when running a stored procedure that uses the memory optimized tables.
The development server has 32 GB. The maximum server memory is set to 25 GB. Currently, the allocated, used and unused (data + index) memory by memory optimized tables are:
* Allocated: 2755 MB
* Used: 1663 MB
* Unused: 1092 MB
I did not expect the server running out of memory from the pool with only 2.7GB of memory usage from memory-optimized tables out of 25GB available to SQL Server,
How often does SQL server performs garbage collection from the memory optimized tables? Is it possible to run those on demand? If so, what would be the performance penalty in doing so?