SQL Server In-Memory OLTP Garbage Collection

1. In-memory database can be bound to resource pool to limit the memory it maximum can use. say, limit the resource pool to have 20% of memory, then the associated in-memory database won't grow over the limit. operation on the db might fail, but it won't fail the entire instance.

2. garbage collection happens when retrieving data and when sql server fails the memory pressure. I feel this is similar to Oracle's row versioning management inn term of updated data block contains all information, but I need to check the book before say it for sure.

3. garbage collection uses same term of generation as what in JVM's GC. In SQL Server, there are 16 generations/queues, the first 15 queues contain 16 transaction ids in each queue (the last queue piles up what ever newer coming transaction ids?). by this design, SQL Server can deal with 16 transaction at one time on GC. after g0 is done, g1 shifts to g0.

4. there are DMvs to monitor the usage of GC

• sys.dm_xtp_gc_stats
• sys.dm_xtp_gc_queue_stats
• sys.dm_db_xtp_gc_cycle_stats – Database Level 

5. perfmon can also monitor the GC statistics