Migration Candidates, Memory Sizing, Garbage Collection, and Operational Monitoring

Learning outcomes

A successful proof-of-concept can fail in production if memory sizing and version cleanup are treated as afterthoughts. Memory-optimized rows, historical row versions, indexes, hash bucket arrays and system metadata all need memory; durable objects also need checkpoint-file storage and log throughput. The correct migration question is therefore not “how large is this table on disk?” but “what concurrency problem are we solving, what live and historical memory will the engine need, what durable storage/recovery load follows, and what happens at the quota boundary?”

1. Candidate selection starts with a measured bottleneck

Good candidates are usually short OLTP transactions where lock/latch contention, tempdb pressure from transient table structures, or high-frequency interpreted code has been identified as a material constraint. A table is not a candidate merely because it is “important,” small enough to fit in memory, or frequently queried. Ordinary disk tables already benefit from the buffer pool, so a read-mostly table whose pages are cached might gain little from a storage-engine migration.

Collect evidence on the existing rowstore design first. Operational index statistics can show row/page lock waits and latch waits; wait statistics and Query Store can establish whether those waits actually align with user-visible latency. Make sure indexing and transaction scope are already reasonable, or you risk comparing a tuned XTP design with an intentionally weak baseline.

USE ServiceHubLab;GOSELECT  OBJECT_SCHEMA_NAME(ios.object_id) AS schema_name,  OBJECT_NAME(ios.object_id) AS table_name,  i.name AS index_name,  ios.row_lock_wait_count,  ios.row_lock_wait_in_ms,  ios.page_lock_wait_count,  ios.page_lock_wait_in_ms,  ios.page_latch_wait_count,  ios.page_latch_wait_in_msFROM sys.dm_db_index_operational_stats (DB_ID(), OBJECT_ID(N'ops.WorkOrder'), NULL, NULL) AS iosJOIN sys.indexes AS i  ON i.object_id=ios.object_id AND i.index_id=ios.index_idORDER BY ios.page_latch_wait_in_ms DESC, ios.row_lock_wait_in_ms DESC;GO

These counters are cumulative and reset with engine/object lifecycle events. They establish observed waiting, not causation by themselves. Correlate them with workload timing and plan/transaction evidence before redesigning storage.

2. Memory sizing includes indexes, versions and fixed arrays

For a memory-optimized table, the rough live footprint is row memory plus every index. Hash indexes are particularly easy to underestimate because their bucket arrays are allocated according to the configured bucket count, rounded up to the next power of two, with approximately eight bytes per bucket before considering row/index links. Memory-optimized nonclustered indexes scale more with key size and row count. Row headers also contain timestamp/version and index-link metadata.

USE master;GOIF DB_ID(N'ServiceHubXtpLab') IS NULLBEGIN    CREATE DATABASE ServiceHubXtpLab;END;GOALTER DATABASE ServiceHubXtpLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubXtpLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubXtpLab;GOIF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = 'FX')BEGIN    ALTER DATABASE ServiceHubXtpLab      ADD FILEGROUP ServiceHubXtpFG CONTAINS MEMORY_OPTIMIZED_DATA;    DECLARE @base nvarchar(4000) = CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath'));    IF @base IS NULL        THROW 50001, 'InstanceDefaultDataPath is unavailable. Supply a writable SQL Server data path manually.', 1;    DECLARE @folder nvarchar(4000) = @base + N'ServiceHubXtpContainer';    DECLARE @sql nvarchar(max) =      N'ALTER DATABASE ServiceHubXtpLab ADD FILE '      + N'(NAME=N''ServiceHubXtpContainer'', FILENAME=N'''      + REPLACE(@folder,'''','''''')      + N''') TO FILEGROUP ServiceHubXtpFG;';    EXEC sys.sp_executesql @sql;END;GO

USE ServiceHubXtpLab;GOSELECT  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_kbFROM sys.dm_db_xtp_table_memory_statsORDER BY memory_allocated_for_table_kb + memory_allocated_for_indexes_kb DESC;GOSELECT  OBJECT_NAME(h.object_id) AS table_name,  i.name,  h.bucket_countFROM sys.hash_indexes AS hJOIN sys.indexes AS i  ON i.object_id=h.object_id AND i.index_id=h.index_idORDER BY h.bucket_count DESC;GO

Plan for peak active rows, normal version churn, indexes, transient growth during migrations/DDL, and safety headroom. Never size exactly to today's steady-state DMV result. Standard and Express impose per-database memory-optimized-data quotas, so approaching 32 GB or 352 MB respectively can produce quota errors such as 41823. Enterprise removes the edition-specific cap, not the physical-memory requirement.

3. Garbage collection is version cleanup, not checkpoint-file cleanup

When an in-memory row is updated or deleted, older versions cannot be reclaimed until no active transaction could still need them. The In-Memory OLTP garbage collector identifies obsolete row versions and work is processed by transaction schedulers and an idle worker. A long-running transaction can therefore hold back cleanup and inflate memory even though the business table's current row count looks stable.

SELECT  transaction_id,  session_id,  begin_tsn,  end_tsn,  state_descFROM sys.dm_db_xtp_transactionsORDER BY begin_tsn;GOSELECT  queue_id,  current_queue_depth,  total_enqueues,  total_dequeuesFROM sys.dm_xtp_gc_queue_statsORDER BY current_queue_depth DESC;GOSELECT * FROM sys.dm_xtp_gc_stats;GO

Do not interpret a nonzero GC queue as a failure: work is expected to flow through the queues. Investigate when queue depth remains elevated, memory grows, or old active transactions prevent progress. Killing sessions solely because a queue is nonzero is as misguided as rebuilding an index because fragmentation crossed an arbitrary number.

Checkpoint-file merging is a separate persistent-storage lifecycle. Memory garbage collection can reclaim obsolete row versions before the corresponding on-disk data/delta files become removable. Checkpoints, merge policy and transaction-log truncation determine when persistent checkpoint files transition and can be deleted.

SELECT  state_desc,  file_type_desc,  COUNT(*) AS file_count,  SUM(file_size_in_bytes)/1024.0/1024.0 AS allocated_mb,  SUM(file_size_used_in_bytes)/1024.0/1024.0 AS used_mbFROM sys.dm_db_xtp_checkpoint_filesGROUP BY state_desc,file_type_descORDER BY state_desc,file_type_desc;GODBCC SQLPERF(LOGSPACE);GO

4. Backup, restore, HA and recovery memory are part of sizing

Regular SQL Server backups include durable memory-optimized data. A full backup contains relevant data/delta checkpoint-file contents plus the active log; differential backups apply their own checkpoint-file selection rules. During restore or crash recovery, durable memory-optimized data must be loaded back into memory before the database is fully available. If sufficient memory is unavailable, recovery can fail and the database can become suspect. Recovery-time objective (RTO) therefore includes storage throughput and memory reconstruction, not just backup file size.

In-Memory OLTP integrates with Availability Groups and Failover Cluster Instances. AG secondaries can maintain durable memory-optimized state in memory, but SCHEMA_ONLY rows are empty after failover by design. In an FCI, the new node must load durable memory-optimized data during recovery, so failover time can be longer. These differences belong in HA drills.

SELECT  SUM(file_size_in_bytes)/1024.0/1024.0 AS xtp_checkpoint_allocated_mb,  SUM(file_size_used_in_bytes)/1024.0/1024.0 AS xtp_checkpoint_used_mbFROM sys.dm_db_xtp_checkpoint_files;GOSELECT  SUM(memory_used_by_table_kb + memory_used_by_indexes_kb)/1024.0 AS xtp_used_mbFROM sys.dm_db_xtp_table_memory_stats;GO

A large ratio between checkpoint storage and current in-memory size is not automatically corruption. It can be normal append/merge lifecycle, especially during heavy churn. But persistent growth warrants review of checkpoint/log-backup progress and merge state.

5. Deliberately wrong approach: migrate first, size later

Moving a hot 30-GB workload into SQL Server Standard because the current disk table is 28 GB ignores indexes, row versions, hash arrays and growth. The 32-GB per-database quota leaves almost no operating margin. Under load, inserts/updates can fail at the quota boundary. The repair is capacity modeling before migration: include expected peak rows, every index, churn/version headroom and business growth, then validate with a scaled workload and monitor actual XTP memory.

If the design cannot maintain safe headroom, choose a smaller candidate set, keep the table disk-based, partition the business workload deliberately, or use a deployment whose memory/edition boundaries fit. Do not treat an edition upgrade as the only architecture answer.

6. Production judgment and bridge

A credible adoption plan includes baseline waits, candidate workload, memory model, growth/headroom, checkpoint/log storage, backup size, restore/failover timings, quota alarms and rollback. Keep a disk-based representation/cutover plan until production behavior is understood. Chapter 20's final lesson combines these constraints into a decision: whether XTP is actually better than a competent rowstore implementation for the ServiceHub workload.

Authoritative references

• Estimate memory requirements for memory-optimized tables
• Memory-optimized table system views
• sys.dm_xtp_gc_queue_stats
• Back up databases with memory-optimized tables
• High availability support for In-Memory OLTP databases