Memory-Optimized Tables, Hash vs Range Indexes, Durability Options, and Checkpoint Files

Learning outcomes

ServiceHub has a small dispatch table that is updated thousands of times per minute during peak routing. A conventional rowstore design is already indexed correctly, yet short transactions still collide on hot pages and keys. The tempting answer is “put the table in RAM.” In-Memory OLTP is more specific: it is a separate storage and transaction engine with memory-resident rows, lock- and latch-free indexes, multiversion optimistic concurrency, distinct durable checkpoint files, and optional native compilation. It is not the ordinary buffer pool with a different switch.

1. Mental model: no 8-KB data pages, but durability still has storage

A memory-optimized table stores its active rows in memory as row versions linked through memory-optimized indexes. It does not use the 8-KB page and B-tree latch model used by ordinary rowstore tables. That removes a class of buffer-latch and lock-management work, but it does not mean durable data exists only in RAM. A SCHEMA_AND_DATA table writes durable changes to the normal transaction log and persists inserted/deleted-row information in append-only checkpoint files inside a MEMORY_OPTIMIZED_DATA filegroup. During restart or restore, SQL Server reconstructs the in-memory structures from checkpoint files plus the log.

A SCHEMA_ONLY table has durable metadata but non-durable rows. After database restart, failover, or restore/recovery, the table exists and is empty. That can be excellent for caches, staging or session-state designs whose data is reconstructible, but disastrous for a work queue if the application expects queued work to survive a crash. Durability is therefore a business requirement, not a performance knob.

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

The lab uses a separate database because a memory-optimized filegroup is an architectural database choice rather than a throwaway table property. Keeping the experiment in ServiceHubXtpLab makes rollback unambiguous: once the chapter is complete and no lab session is connected, the entire database can be dropped. The file path is derived from SQL Server's configured default data path, so the engine—not your desktop shell—owns the container.

2. Equality and range access need different index structures

A memory-optimized hash index maps an exact key value to a bucket. It is ideal for equality predicates such as dispatch_id = @id, but it has no useful key order for >, BETWEEN, prefix ordering, or ORDER BY. A memory-optimized nonclustered index is an ordered, latch-free tree structure and supports range and ordered access. Calling the second structure “range index” is convenient shorthand; the T-SQL keyword is NONCLUSTERED.

USE ServiceHubXtpLab;GODROP TABLE IF EXISTS dbo.DispatchScratch;DROP TABLE IF EXISTS dbo.DispatchQueue;GOCREATE TABLE dbo.DispatchQueue(  dispatch_id  bigint       NOT NULL,  region_code  char(3)      NOT NULL,  due_at       datetime2(0) NOT NULL,  status       varchar(16)  NOT NULL,  payload      nvarchar(200) NULL,  CONSTRAINT PK_DispatchQueue    PRIMARY KEY NONCLUSTERED HASH (dispatch_id)    WITH (BUCKET_COUNT = 65536),  INDEX IX_DispatchQueue_Due NONCLUSTERED (due_at, region_code))WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);GOCREATE TABLE dbo.DispatchScratch(  session_id int NOT NULL,  item_id    int NOT NULL,  note       nvarchar(100) NULL,  INDEX IX_DispatchScratch HASH (session_id, item_id)    WITH (BUCKET_COUNT = 4096))WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);GOINSERT dbo.DispatchQueue(dispatch_id,region_code,due_at,status,payload)VALUES (1,'N01',DATEADD(minute,5,SYSUTCDATETIME()),'READY',N'router-a'), (2,'N01',DATEADD(minute,15,SYSUTCDATETIME()),'READY',N'router-b'), (3,'E03',DATEADD(minute,10,SYSUTCDATETIME()),'READY',N'router-c');GO

The hash bucket array is allocated independently of the number of rows. SQL Server rounds the requested bucket count up to a power of two, and each bucket consumes memory even when empty. For an equality key with many distinct values, Microsoft guidance generally aims for roughly one to two times the expected distinct-key count; exact sizing still comes from workload evidence. Grossly oversized hashes waste memory, while heavy collisions create long chains and increase lookup work.

SELECT  OBJECT_NAME(hs.object_id) AS table_name,  i.name AS index_name,  hs.total_bucket_count,  hs.empty_bucket_count,  hs.avg_chain_length,  hs.max_chain_lengthFROM sys.dm_db_xtp_hash_index_stats AS hsJOIN sys.indexes AS i  ON i.object_id=hs.object_id AND i.index_id=hs.index_idWHERE hs.object_id IN (OBJECT_ID(N'dbo.DispatchQueue'), OBJECT_ID(N'dbo.DispatchScratch'));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_statsWHERE object_id IN (OBJECT_ID(N'dbo.DispatchQueue'), OBJECT_ID(N'dbo.DispatchScratch'));GO

An empty-bucket percentage by itself is not a grade. The right question is whether equality lookups remain short-chain and whether the fixed array is a sensible fraction of the table's memory budget. Likewise, a range query should use the ordered nonclustered index rather than being forced through a hash structure merely because “hash is in-memory.”

3. Checkpoint files are durable storage, not a second copy of the buffer pool

SELECT file_id, name, type_desc, physical_nameFROM sys.database_filesORDER BY file_id;GOCHECKPOINT;GOSELECT  file_type_desc,  state_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 file_type_desc, state_descORDER BY file_type_desc, state_desc;GO

Durable memory-optimized storage uses append-only data files for inserted rows and delta files that reference deleted rows. You can also see root or large-data file types on modern versions. Files move through states such as PRECREATED, UNDER CONSTRUCTION, ACTIVE, MERGE TARGET and WAITING FOR LOG TRUNCATION. A CHECKPOINT can advance persistence, but production file lifecycle also depends on log truncation and the normal backup strategy. Do not run checkpoints or log backups as a superstitious “cleanup” job without understanding the database's recovery design.

The on-disk checkpoint footprint can be materially larger than active in-memory rows because data/delta files are append-oriented and merge asynchronously. Microsoft notes that active checkpoint storage can approach roughly twice the durable in-memory table size under normal merge behavior. That makes storage capacity and recovery throughput part of an In-Memory OLTP design.

4. Deliberately wrong approach: use SCHEMA_ONLY for business-critical work

Suppose an engineer changes the durable queue to SCHEMA_ONLY because a benchmark shows less log I/O. The queue looks healthy until restart or failover, when every queued row disappears by design. Nothing is “corrupted”; the durability contract was wrong. The repair is to keep reconstructible caches/scratch data in SCHEMA_ONLY objects and business state in SCHEMA_AND_DATA objects, then test restart/restore/failover behavior explicitly.

SELECT  s.name AS schema_name,  t.name AS table_name,  t.is_memory_optimized,  t.durability_descFROM sys.tables AS tJOIN sys.schemas AS s ON s.schema_id=t.schema_idWHERE t.is_memory_optimized=1ORDER BY t.name;GO

5. Production judgment

Adopt memory-optimized tables only after identifying a concurrency or latency problem that their lock/latch-free data structures actually address. Equality-heavy access can favor hash indexes; range/order access needs nonclustered memory-optimized indexes. Budget row versions and every index, not only the business columns. Standard and Express quotas make this especially visible, but Enterprise still cannot allocate memory that the operating system does not have. Durable tables also consume log throughput, checkpoint storage and recovery bandwidth.

Do not compare a memory-optimized table with an intentionally bad rowstore design. First establish a competent disk-based baseline: correct keys, indexes, transaction scope, statistics, memory and log configuration. Then compare representative concurrency and durability. The next lesson adds native compilation and asks a separate question: even when the table engine is memory-optimized, is compiling stored procedure logic to native machine code beneficial for this workload?

Authoritative references

• In-Memory OLTP overview and usage scenarios
• SQL Server 2025 editions and supported features
• Durability for memory-optimized tables
• Hash indexes for memory-optimized tables
• sys.dm_db_xtp_checkpoint_files