Cannot reclaim Index Unused Memory in In-Memory OLTP

Steps to reproduce the problem

Create a database with memory-optimized filegroup and container
Create schema only in-memory table with nonclustered pk
Simulate insert and delete activity.
My result is that I have high index unused memory that won't go down.

USE master
 go
 DROP DATABASE IF EXISTS MemoryOptimizedTest
 CREATE DATABASE MemoryOptimizedTest
 GO
 USE MemoryOptimizedTest
 GO
 ALTER DATABASE MemoryOptimizedTest 
 ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA
 GO 
 
 ALTER DATABASE MemoryOptimizedTest ADD FILE (name='imoltp_mod1', filename='c:\imoltp_mod1') TO FILEGROUP imoltp_mod
 GO
 
 
 DROP TABLE IF EXISTS dbo.MyCache
 CREATE TABLE dbo.MyCache
 (
    PK int NOT NULL, 
    SecondInt int NOT NULL,
    ThirdInt int NOT NULL,
     CONSTRAINT PK_MyCache PRIMARY KEY NONCLUSTERED (PK)
 ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY)
 
 go

/* Generate activity and monitor table size */
USE MemoryOptimizedTest
go

SELECT
    object_id,
    OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) AS Table_Name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb,
    memory_allocated_for_indexes_kb,
    memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE OBJECT_ID = OBJECT_ID('dbo.MyCache')

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
, tally AS (SELECT TOP (10000) n FROM Nums ORDER BY n)
INSERT INTO dbo.MyCache (PK, SecondInt, ThirdInt)
SELECT 
    n
    , n+1
    , n+2
FROM tally 

WAITFOR DELAY '00:00:02'
DELETE FROM dbo.MyCache

GO 50

When I run it on my localmachine Microsoft SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64) Developer Edition
with 16

I read somewhere that the garbage collector doesn't free up space until it faces memory pressure but it seems weird that it would hold so much unused memory.

Is it weird, though? Why do garbage collection now when it can be put off until later :)

From the documentation, a confirmation of your thought (emphasis mine):

After a user transaction commits, it identifies all queued items associated with the scheduler it ran on and then releases the memory. If the garbage collection queue on the scheduler is empty, it searches for any non-empty queue in the current NUMA node. If there is low transactional activity and there is memory pressure, the main garbage-collection thread can access garbage collect rows from any queue. If there is no transactional activity after (for example) deleting a large number of rows and there is no memory pressure, the deleted rows will not be garbage collected until the transactional activity resumes or there is memory pressure.

Your test workload seems quite small and I wouldn't be surprised if it isn't enough to trigger any collection, especially with how robust that system's memory resources are.

With 2TB of memory, I wouldn't sweat < 500MB of memory usage for this purpose, nor would I worry about this until it became an actual problem.

StackExchange Database Administrators Q#275494, answer score: 5