Memory-Optimized Table Memory Allocated issues

I have Some In-Memory OLTP objects in SQL Server and it's version SQL Server 2016 SP2-CU4.
I have a problem because of data row-size of some tables greater than 8060bytes, so there was some off-row storage in internal xtp DMVs and those tables allocated a memory about 120GB.

I alter that tables and reduce row-size to less than 8060 bytes, the memory allocated to those tables reduce and the allocated memory to object_id=0 is being grow in sys.dm_db_xtp_table_memory_stats and it's about 50GB. I think The garbage collector thread cant find this orphaned object , thus it cant deallocate memory.

another issue is 'Memory Allocated To Memory Optimized Objects' is 25GB where 'Memory Used By Memory Optimized Objects' is 62GB, may be I think this is a bug

how I can deallocate this memory ?!

You should check out this blog post from the king of In-Memory OLTP, Ned Otter:

Row version lifecycle for In-Memory OLTP

In particular, this passage:

1. Deallocating rows from memory

...


So the two triggers for Garbage Collection are memory pressure and/or transactional activity. Conversely, that means if there’s no memory pressure – or transactional activity is low – it’s perfectly reasonable to have row versions that aren’t garbage collected. There’s also no way to force garbage collection to occur.

So this is just to clarify that there are situations where garbage collection may not occur, or may occur slowly.

The post goes on to discuss that long-running queries on in-memory tables will completely block garbage collection, so make sure to check for any of those.

The reason the screenshot of SSMS is off (used it higher than allocated) is because it's purely based on the DMVs, so the NULL memory_allocated_for_table_kb throws off the calculation. This is what runs when you open the database properties window:

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_allocated_for_table_kb) + sum(tms.memory_allocated_for_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryAllocatedToMemoryOptimizedObjectsInKB]

SELECT
    isnull((
        select convert(decimal(18,2),(sum(tms.memory_used_by_table_kb) + sum(tms.memory_used_by_indexes_kb)))
        from [sys].[dm_db_xtp_table_memory_stats] tms)
    , 0.00) AS [MemoryUsedByMemoryOptimizedObjectsInKB]

I'm still not really sure what the purpose of that object_id = 0 row is in dm_db_xtp_table_memory_stats. I can repeat the behavior locally (create a table with LOB data, then ALTER the column to fit in-row, suddenly I have that same 0 result), but I don't know if it's a problem.

It's possible that it's normal overhead related to in-memory OLTP, but it might be a bug. If it's causing significant problems, I'd suggest opening a case with Microsoft. There have been bugs before related to memory usage with LOB data, see MSDN Forums | In Memory OLTP for an example, and this CU: FIX: Out-of-memory error when you run a query to access LOB columns through In-Memory OLTP in SQL Server 2016.

StackExchange Database Administrators Q#225447, answer score: 5