When In-Memory OLTP Helps—and When Well-Tuned Disk-Based Tables Are Better

Learning outcomes

In-Memory OLTP is now technically available, but the engineering decision is still open. The correct conclusion can be “keep the disk-based table.” ServiceHub needs a repeatable comparison that preserves the same business contract, uses representative concurrency and durability, records operational complexity, and treats rollback as part of the design. A specialized engine is valuable only when its end-to-end benefits exceed its memory, migration and operating costs.

1. Start with equivalent schemas, not a straw-man rowstore

The disk-based baseline deserves appropriate indexes. The XTP version deserves appropriately chosen hash and ordered indexes. Both must implement the same durability and business semantics. If the disk design is missing a primary key or uses an obviously poor scan path, any “win” for XTP is meaningless.

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;GOUSE ServiceHubXtpLab;GOIF OBJECT_ID(N'dbo.DispatchQueue') IS NULLBEGIN  CREATE 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);END;GO

USE ServiceHubXtpLab;GODROP TABLE IF EXISTS dbo.DispatchQueueDisk;GOCREATE TABLE dbo.DispatchQueueDisk(  dispatch_id bigint NOT NULL CONSTRAINT PK_DispatchQueueDisk PRIMARY KEY,  region_code char(3) NOT NULL,  due_at datetime2(0) NOT NULL,  status varchar(16) NOT NULL,  payload nvarchar(200) NULL);CREATE INDEX IX_DispatchQueueDisk_DueON dbo.DispatchQueueDisk(due_at,region_code);GO;WITH n AS(  SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n  FROM sys.all_objects a CROSS JOIN sys.all_objects b)INSERT dbo.DispatchQueueDisk(dispatch_id,region_code,due_at,status,payload)SELECT 100000+n,       CASE n%4 WHEN 0 THEN 'N01' WHEN 1 THEN 'S02' WHEN 2 THEN 'E03' ELSE 'W04' END,       DATEADD(second,n,'2026-01-01T00:00:00'),       'READY',       CONCAT(N'payload-',n)FROM n;GO

Populate the memory-optimized table with equivalent rows in a fresh lab run. If Express memory capacity is tight, reduce the data set rather than pushing the quota. Benchmark results only apply to the chosen scale, so record it.

IF OBJECT_ID(N'dbo.DispatchQueue') IS NULL  THROW 50030,'Run Lesson 1 setup first.',1;GODELETE dbo.DispatchQueue WHERE dispatch_id>=100001;INSERT dbo.DispatchQueue(dispatch_id,region_code,due_at,status,payload)SELECT dispatch_id,region_code,due_at,status,payloadFROM dbo.DispatchQueueDisk;GOSELECT 'disk' AS storage_kind,COUNT(*) AS rows FROM dbo.DispatchQueueDiskUNION ALLSELECT 'memory_optimized',COUNT(*) FROM dbo.DispatchQueue;GO

2. Define workload classes before collecting timing

ServiceHub has at least three materially different access patterns: point claims by dispatch ID, “next due” range scans by time/region, and writes that move status repeatedly. The hash index should help equality access; the nonclustered XTP index supports the range. A native procedure might reduce CPU for a short repeated claim operation. But an analytical report that scans thousands of rows may still belong on rowstore/columnstore rather than in a single-threaded native procedure.

DECLARE @id bigint=105000;DECLARE @from datetime2(0)='2026-01-01T01:00:00';DECLARE @to   datetime2(0)='2026-01-01T02:00:00';SELECT dispatch_id,statusFROM dbo.DispatchQueueDiskWHERE dispatch_id=@id;SELECT dispatch_id,statusFROM dbo.DispatchQueueWHERE dispatch_id=@id;SELECT COUNT(*) AS due_rows_diskFROM dbo.DispatchQueueDiskWHERE due_at>=@from AND due_at<@to AND region_code='N01';SELECT COUNT(*) AS due_rows_xtpFROM dbo.DispatchQueueWHERE due_at>=@from AND due_at<@to AND region_code='N01';GO

First verify equal results. Then measure from the client because XTP does not map neatly onto rowstore logical-read metrics. Record client-side elapsed time, throughput and percentile latency for the same statement mix and connection behavior. Include error/retry counts. Run enough warm-up to distinguish one-time compilation/cache effects, then disclose whether the test is cold/warm and whether native procedures have already compiled after restart.

SELECT  @@VERSION AS engine_version,  SERVERPROPERTY('Edition') AS edition,  SERVERPROPERTY('ProductVersion') AS product_version,  DATABASEPROPERTYEX(DB_NAME(),'Updateability') AS updateability,  (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID()) AS compatibility_level,  (SELECT COUNT(*) FROM dbo.DispatchQueueDisk) AS disk_rows,  (SELECT COUNT(*) FROM dbo.DispatchQueue) AS xtp_rows;GO-- Record externally for each run:-- clients/concurrency | operations | duration | p50/p95/p99 latency-- success count | 4130x conflicts/retries | CPU | log bytes/second | memory

3. Concurrency and durability can reverse a single-session result

A single session under warm cache may show little difference because a well-indexed rowstore lookup is already fast. Under many concurrent writers, XTP can remove row/page locking and latching overhead—but if all clients update the same row, optimistic conflicts can rise and retries may erase the benefit. The benchmark therefore needs the same key distribution and business contention as production.

Durability must also match. Comparing a SCHEMA_ONLY table to a fully durable disk table is comparing different products. For a business queue, use SCHEMA_AND_DATA on both sides and keep normal transaction durability unless the application has separately accepted delayed-durability loss semantics.

SELECT  OBJECT_NAME(object_id) AS xtp_table,  memory_used_by_table_kb,  memory_used_by_indexes_kbFROM sys.dm_db_xtp_table_memory_statsORDER BY memory_used_by_table_kb + memory_used_by_indexes_kb DESC;GOSELECT  OBJECT_NAME(object_id) AS table_name,  total_bucket_count,empty_bucket_count,avg_chain_length,max_chain_lengthFROM sys.dm_db_xtp_hash_index_statsORDER BY avg_chain_length DESC;GOSELECT * FROM sys.dm_xtp_transaction_stats;GO

4. Operational feature boundaries belong in the decision

Memory-optimized tables participate in backup/restore and SQL Server HA, but they introduce specific recovery behavior. Durable rows must fit in memory during recovery. SCHEMA_ONLY rows disappear after restart/failover. A natively compiled procedure has a restricted T-SQL/query surface and cannot directly access a disk-based table. Cross-database and distributed transactions cannot access memory-optimized tables in the same way ordinary disk tables can. These are architectural constraints even if a microbenchmark looks excellent.

Migration can also affect schema features, ETL, CDC/replication designs and operational tooling. Current SQL Server supports many features that early In-Memory OLTP versions did not, so do not use SQL Server 2014 folklore as a compatibility matrix. Run a schema/feature inventory against the target 2025 build and test every production dependency.

5. Decision matrix: XTP, native code, or keep rowstore?

A defensible “no” is a successful engineering outcome. Specialized engines add cognitive and operating cost. Keep them only where measured benefits survive realistic concurrency, restart/recovery, backup, failover and schema-change tests.

6. Migration and rollback runbook

For a real table, build a new memory-optimized schema rather than assuming an in-place storage flip. Rehearse data copy and synchronization, validate row counts and business invariants, pause/cut over writers through a controlled application release, monitor memory/conflicts/log/latency, and retain the old rowstore path until rollback criteria expire. If rollback triggers, stop new XTP writes, reconcile/copy authoritative changes back under a defined maintenance window, redirect callers, and verify before dropping any object.

USE master;GOIF DB_ID(N'ServiceHubXtpLab') IS NOT NULLBEGIN  ALTER DATABASE ServiceHubXtpLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;  DROP DATABASE ServiceHubXtpLab;END;GO-- This removes every Chapter 20 lab object and the MEMORY_OPTIMIZED_DATA-- container. It does not modify the established ServiceHubLab database.

Chapter 21 shifts from specialized storage to operational governance: SQL Server Agent jobs, schedules, proxies, integrity checks, evidence-driven maintenance and idempotent runbooks.

Authoritative references

• In-Memory OLTP overview and usage scenarios
• Plan adoption of In-Memory OLTP
• Survey initial areas in In-Memory OLTP
• Backing up a database with memory-optimized tables
• High availability support for In-Memory OLTP databases