SQL Server In-Memory OLTP readiness checklist: memory sizing, durability, hash buckets, retries and operations

#Who this is for

You have a hot table with latch contention, a staging table that is truncated and reloaded every few minutes, or a session-state store that hammers tempdb. Memory-optimized tables can remove latching and locking entirely, but they change how memory, transactions, backups and failover behave. Work through this list before the first CREATE TABLE ... MEMORY_OPTIMIZED = ON in production.

Caution

Once a database has a MEMORY_OPTIMIZED_DATA filegroup, you can't remove it. Dropping every memory-optimized object does not help; the only way back is to move the data into a new database. Decide in a test copy, not in production.

#1. Workload fit

• The bottleneck is proven to be latching, locking or log contention on specific tables (PAGELATCH_EX, LCK_M_*, WRITELOG in wait stats), not IO or bad plans.
• The candidate tables are small enough to fit in memory with room to grow, and the hot set is a small number of tables.
• Access is mostly point lookups and short transactions. Large scans and big analytical queries gain little.
• You know which features the tables need. Check the current list of unsupported features for your version: some data types, triggers, foreign keys, computed columns and ALTER TABLE options have been added over releases, others still aren't supported.
• You have considered the simpler fixes first: OPTIMIZE_FOR_SEQUENTIAL_KEY for last-page inserts, or memory-optimized tempdb metadata for tempdb system-table contention.

-- Contention evidence on the candidate table
SELECT OBJECT_NAME(s.object_id) AS table_name, i.name AS index_name,
       s.page_latch_wait_count, s.page_latch_wait_in_ms,
       s.row_lock_wait_count, s.row_lock_wait_in_ms
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) s
JOIN sys.indexes i ON i.object_id = s.object_id AND i.index_id = s.index_id
ORDER BY s.page_latch_wait_in_ms + s.row_lock_wait_in_ms DESC;

#2. Edition and platform

• Edition confirmed. Standard and Express support In-Memory OLTP from SQL Server 2016 SP1, but with a per-database memory quota. Check the current limits for your edition.
• On Azure SQL Database, the service tier supports it (Premium and Business Critical). The amount of In-Memory OLTP storage scales with the service objective; monitor xtp_storage_percent in sys.dm_db_resource_stats.
• On Azure SQL Managed Instance, the Business Critical tier is used.
• Downgrade paths checked: a database with memory-optimized objects can't be restored to a tier or edition that lacks the feature.

#3. Memory budget

Memory-optimized tables can't be paged out. If they run out of memory, inserts and updates fail; reads keep working.

• Budget about twice the expected data and index size for row versions and growth. Size from a load test, not the formula alone.
• max server memory leaves room for the buffer pool after the memory-optimized data is loaded.
• On Enterprise, the database is bound to its own Resource Governor pool, so a runaway table can't take memory from the buffer pool or other databases.

CREATE RESOURCE POOL pool_imoltp WITH (MIN_MEMORY_PERCENT = 25, MAX_MEMORY_PERCENT = 25);
ALTER RESOURCE GOVERNOR RECONFIGURE;

EXEC sp_xtp_bind_db_resource_pool @database_name = N'Orders', @pool_name = N'pool_imoltp';

-- Binding takes effect when the database next comes online
ALTER DATABASE Orders SET OFFLINE WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Orders SET ONLINE;

Warning

SET OFFLINE WITH ROLLBACK IMMEDIATE disconnects every session and rolls back open transactions. Do it in a maintenance window. In an Availability Group, the binding applies per replica, so bind on each one.

• Monitoring in place for memory use per table and per pool:

SELECT 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
ORDER BY memory_allocated_for_table_kb DESC;

SELECT name, used_memory_kb, max_memory_kb
FROM sys.dm_resource_governor_resource_pools;

Alert well before the pool's limit. A long-running transaction keeps old row versions alive, so memory can climb even when the row count doesn't.

#4. Storage and durability

• Filegroup and container created on fast storage with free space for checkpoint files, which grow larger than the in-memory data while merges catch up:

ALTER DATABASE Orders ADD FILEGROUP Orders_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE Orders ADD FILE (NAME = 'Orders_mod1', FILENAME = 'E:\SQLData\Orders_mod1')
    TO FILEGROUP Orders_mod;   -- FILENAME is a folder, not a file

• Durability chosen per table:

• Every SCHEMA_ONLY table has an owner who has signed off that the rows vanish on restart and AG failover. It isn't written to the log, so it isn't on any secondary.
• Recovery time tested. Database startup loads all durable memory-optimized data into memory before the database is online, so a large table lengthens restarts and failovers.

#5. Index design

• Every table has at least one index; there is no heap, and the primary key is required for durable tables.
• Hash indexes only for equality lookups on the full key. Range scans, ORDER BY and partial-key seeks need a nonclustered (range) index.
• BUCKET_COUNT set to one to two times the expected number of distinct key values. It is rounded up to a power of two.

CREATE TABLE dbo.SessionState
(
    SessionId  uniqueidentifier NOT NULL
        PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 4194304),
    UserId     int NOT NULL,
    Payload    varbinary(8000) NOT NULL,
    ExpiresAt  datetime2 NOT NULL,
    INDEX ix_expires NONCLUSTERED (ExpiresAt)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

• Bucket health checked after load testing:

SELECT OBJECT_NAME(h.object_id) AS table_name, i.name AS index_name,
       h.total_bucket_count, h.empty_bucket_count,
       h.avg_chain_length, h.max_chain_length
FROM sys.dm_db_xtp_hash_index_stats h
JOIN sys.indexes i ON i.object_id = h.object_id AND i.index_id = h.index_id;

Long average chains with many empty buckets mean many duplicate key values; use a range index instead. Long chains with few empty buckets mean too few buckets; rebuild with ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = n), which is an offline operation that needs extra memory.

#6. Transactions and application code

Memory-optimized tables use optimistic multi-version concurrency. Writers don't block; conflicting writers fail.

• The application retries the In-Memory OLTP conflict and validation errors, at least 41302 (update conflict), 41305 and 41325 (validation failures), 41301 (dependency failure) and 1205 (deadlock in mixed transactions), with a small back-off.
• Interpreted T-SQL that touches memory-optimized tables inside explicit transactions uses WITH (SNAPSHOT) table hints, or the database option below. Otherwise it fails under READ COMMITTED.

ALTER DATABASE Orders SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

• Cross-container transactions (disk-based and memory-optimized tables together) are tested for the isolation level combinations you use.
• Natively compiled procedures are reserved for the hottest paths. They are compiled at creation and on first execution after restart, and plans don't recompile when statistics change unless you call sp_recompile.

CREATE PROCEDURE dbo.usp_GetSession @SessionId uniqueidentifier
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT UserId, Payload, ExpiresAt
    FROM dbo.SessionState
    WHERE SessionId = @SessionId;
END;

• Table variables in hot procedures replaced with memory-optimized table types where that removes tempdb contention.

#7. Operations, backup and HA

• Backups include the memory-optimized filegroup automatically; backup size and duration measured with the checkpoint files included.
• DBCC CHECKDB behaviour understood: it doesn't check memory-optimized tables. Checksums on the checkpoint files are verified during backup, so use BACKUP ... WITH CHECKSUM.
• Availability Group secondaries have the same memory and pool binding as the primary. A secondary that can't fit the data can't become primary.
• Schema change process agreed. ALTER TABLE on a memory-optimized table rebuilds it, needs memory for two copies and is offline for the duration.
• Runbook written for "memory-optimized pool full": identify the growing table, find long transactions holding versions, raise the pool limit, purge or archive rows.

#Go / no-go

If any answer is no, stay with disk-based tables and fix the contention another way first.