In-Memory OLTP (Memory-Optimized Tables) in SQL Server: A Complete Guide

---

SQL Server’s In-Memory OLTP engine, codenamed Hekaton, stores tables entirely in memory using lock-free data structures that allow extremely fast concurrent access without the latch and lock overhead that limits traditional disk-based tables. Under high-throughput OLTP workloads, memory-optimized tables with natively compiled stored procedures can deliver order-of-magnitude performance improvements over equivalent disk-based designs.

This guide covers what In-Memory OLTP is, when it is the right choice, how to set it up from scratch, how to measure performance gains, the limitations that must be understood before deployment, and a complete hands-on workshop to practice every concept step by step.

1 What Are Memory-Optimized Tables Beginner

Memory-optimized tables store all rows in RAM rather than on disk pages. The storage structure is fundamentally different from traditional tables: there are no 8KB pages, no extents, no buffer pool, and no page latches. Rows are linked through pointer chains indexed by hash or range indexes that operate without locks or latches.

Concurrent writers do not block each other. Concurrent readers do not block writers. The lock manager is not involved in memory-optimized table operations. This is the core reason for the performance difference: traditional OLTP performance under high concurrency is often limited not by CPU or disk speed but by time spent waiting for locks and latches to be released.

Property	Traditional (Disk-Based) Table	Memory-Optimized Table
Storage location	Buffer pool (disk-backed)	Entirely in RAM
Concurrency mechanism	Locks and latches	Lock-free, latch-free
Page structure	8KB pages, extents	Row pointer chains
Durability	Always durable	Configurable per table
Procedure compilation	Interpreted T-SQL	Native machine code (optional)
Recovery on restart	Log replay	Checkpoint file reload

2 When to Use In-Memory OLTP Beginner

Strong use cases

• High-frequency OLTP operations. Order processing, message queues, telemetry ingestion, gaming leaderboards, financial tick data. Any scenario where thousands of inserts and updates per second create lock contention on traditional tables.
• High concurrent write contention. If wait statistics show LCK or PAGELATCH waits on a specific hot table, that table is a candidate for In-Memory OLTP.
• Session state tables. Web application session stores, gaming server state, microservice caches. The SCHEMA_ONLY durability option makes these extremely fast since no disk writes are required.
• Staging tables in ETL. High-speed intermediate storage during data movement where the data is transient and durability is not required.
• Configuration and lookup caches. Pricing tables, reference data, and frequently-read configuration values that change rarely but are read at extremely high rates.

Poor use cases

• Large historical tables where the data volume exceeds available RAM
• Reporting and analytical workloads (columnstore indexes on disk-based tables are more appropriate)
• Tables with large LOB columns (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX))
• Workloads where memory is already constrained

3 Requirements and Edition Support Beginner

• SQL Server 2014 and later. In-Memory OLTP was introduced in SQL Server 2014. The feature matured significantly in SQL Server 2016 which removed many early limitations. SQL Server 2016 or later is the recommended minimum for production use.
• Enterprise or Developer Edition. In-Memory OLTP requires Enterprise Edition in SQL Server versions prior to 2016 SP1. From SQL Server 2016 SP1 onward it is available in Standard Edition as well, though with memory limits.
• Sufficient RAM. The entire memory-optimized table must fit in RAM. If the server runs out of memory for In-Memory OLTP, insert operations fail. Memory planning must account for peak table size plus growth headroom.
• A MEMORY_OPTIMIZED_DATA filegroup. SQL Server requires a dedicated filegroup for checkpoint files even when DURABILITY = SCHEMA_ONLY. The filegroup must be created before any memory-optimized tables can be created in the database.

4 Create the Memory-Optimized Filegroup Beginner

The filegroup is required even for SCHEMA_ONLY tables. SQL Server uses it to store checkpoint files for durability and recovery. This is a one-time setup per database.

-- Step 1: Add the MEMORY_OPTIMIZED_DATA filegroup to the database
ALTER DATABASE MyDB
ADD FILEGROUP MyDB_InMemory
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

-- Step 2: Add a file to the filegroup
-- The path must exist and be accessible to the SQL Server service account
ALTER DATABASE MyDB
ADD FILE
(
    NAME     = N'MyDB_InMemory1',
    FILENAME = N'D:\Data\MyDB_InMemory1'  -- directory path, not a file path
)
TO FILEGROUP MyDB_InMemory;
GO

-- Verify the filegroup was created correctly
SELECT
    fg.name                 AS FilegroupName,
    fg.type_desc,
    mf.name                 AS LogicalFileName,
    mf.physical_name
FROM sys.filegroups           fg
JOIN sys.master_files         mf ON mf.data_space_id = fg.data_space_id
WHERE fg.type_desc = 'MEMORY_OPTIMIZED_DATA'
  AND mf.database_id = DB_ID('MyDB');

5 Create a Memory-Optimized Table Intermediate

-- Memory-optimized table with a hash index on the primary key
CREATE TABLE dbo.Orders_InMemory
(
    OrderID     INT             NOT NULL
                PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    CustomerID  INT             NOT NULL,
    OrderTotal  DECIMAL(18,2)   NOT NULL,
    CreatedOn   DATETIME2       NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

-- Standard DML works without any syntax changes
INSERT INTO dbo.Orders_InMemory (OrderID, CustomerID, OrderTotal, CreatedOn)
VALUES (1, 42, 199.99, SYSUTCDATETIME());

SELECT * FROM dbo.Orders_InMemory WHERE CustomerID = 42;

Durability options

Option	Behavior	Best For
SCHEMA_AND_DATA	Rows are checkpointed to disk and survive restart	Transactional data that must not be lost
SCHEMA_ONLY	Rows are lost on restart, structure survives	Session state, staging tables, caches

Hash index sizing

The BUCKET_COUNT for a hash index should be set to approximately one to two times the expected number of rows in the table. Too small a bucket count causes hash collisions that degrade seek performance. Too large wastes memory. The bucket count cannot be changed without recreating the index, so plan it carefully. Use a range index (NONCLUSTERED without HASH) when the column is used in range queries or ORDER BY operations.

6 Natively Compiled Stored Procedures Intermediate

Natively compiled stored procedures are compiled to machine code when created rather than being interpreted at runtime. This eliminates the T-SQL interpretation overhead on every execution and produces dramatic CPU and duration reductions for tight transactional loops. They are optional but provide the largest performance gains on memory-optimized tables.

CREATE PROCEDURE dbo.InsertOrder_NC
    @OrderID    INT,
    @CustomerID INT,
    @OrderTotal DECIMAL(18,2)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    INSERT INTO dbo.Orders_InMemory (OrderID, CustomerID, OrderTotal, CreatedOn)
    VALUES (@OrderID, @CustomerID, @OrderTotal, SYSUTCDATETIME());
END;
GO

-- Execute exactly like any other stored procedure
EXEC dbo.InsertOrder_NC @OrderID = 2, @CustomerID = 42, @OrderTotal = 299.99;

7 Limitations That Must Be Understood Intermediate

• Memory is the hard limit. If the server runs out of memory allocated for In-Memory OLTP, insert operations return an error. Unlike disk-based tables where running low on space is a warning, running out of In-Memory OLTP memory causes immediate failures. Capacity planning and memory monitoring are mandatory.
• Hash index bucket count cannot be changed online. The bucket count must be right at table creation time. Resizing requires recreating the index which involves dropping and recreating the table.
• Limited data types in older versions. Modern SQL Server versions support most data types. Large object types (VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX)) are not supported in memory-optimized tables in any version.
• No FOREIGN KEY constraints referencing memory-optimized tables from disk-based tables. Cross-type referential integrity constraints are not supported.
• ALTER TABLE is restricted. Many ALTER TABLE operations that work on disk-based tables are not supported on memory-optimized tables. Schema changes often require recreating the table.
• Replication and some HA features have version-specific support. Check the current Microsoft documentation for the specific SQL Server version before relying on replication or log shipping with memory-optimized tables.

8 Monitoring Memory-Optimized Objects Intermediate

-- Memory consumption by In-Memory OLTP consumers
SELECT
    memory_consumer_type_desc,
    object_name,
    index_id,
    allocated_bytes / 1024 / 1024   AS AllocatedMB,
    used_bytes / 1024 / 1024        AS UsedMB
FROM sys.dm_db_xtp_memory_consumers
ORDER BY allocated_bytes DESC;

-- Transaction statistics for memory-optimized operations
SELECT
    total_user_transactions,
    total_durability_commits,
    total_aborts
FROM sys.dm_db_xtp_transaction_stats;

-- Row counts and memory per table
SELECT
    OBJECT_NAME(object_id)          AS TableName,
    row_count,
    used_object_reserved_page_count * 8 / 1024 AS UsedMB
FROM sys.dm_db_xtp_object_stats
ORDER BY used_object_reserved_page_count DESC;

9 Performance Loss Scenarios Intermediate

In-Memory OLTP does not automatically improve every workload. Performance can degrade or fail in these situations:

• Memory exhaustion. When In-Memory OLTP memory is full, inserts fail with an out-of-memory error. Monitor sys.dm_db_xtp_memory_consumers and alert before hitting the limit.
• Hash index undersized. A BUCKET_COUNT that is too small relative to the row count causes many hash collisions. Point lookups that should be O(1) degrade toward O(n). Query performance degrades as the table grows.
• SCHEMA_AND_DATA on extremely high write rates. Checkpoint file I/O can become a bottleneck on very high sustained write rates when durability is required. Monitor checkpoint file I/O if write throughput is the primary workload.
• Incorrect workload type. Analytical queries with large range scans, aggregations across millions of rows, and complex joins do not benefit from In-Memory OLTP. These workloads belong on columnstore-indexed disk-based tables.

10 Hands-On Workshop: From Setup to Performance Comparison Advanced

Run this workshop in a lab environment on a non-production SQL Server instance. It covers every concept from this article in a practical sequence.

Exercise 1: Create the filegroup and a volatile session cache table

-- Create a lab database if needed
IF DB_ID('LabDB') IS NULL
    CREATE DATABASE LabDB;
GO

USE LabDB;
GO

-- Add the required filegroup
ALTER DATABASE LabDB
ADD FILEGROUP LabDB_InMemory CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE LabDB
ADD FILE (NAME = N'LabDB_InMemory1', FILENAME = N'D:\LabDB_InMemory1')
TO FILEGROUP LabDB_InMemory;
GO

-- Create a SCHEMA_ONLY session cache table (volatile: data lost on restart)
CREATE TABLE dbo.SessionCache
(
    SessionID   NVARCHAR(50)    NOT NULL
                PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),
    UserID      INT             NOT NULL,
    LastActive  DATETIME2       NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

Exercise 2: Create a matching disk-based table for comparison

CREATE TABLE dbo.SessionCache_Disk
(
    SessionID   NVARCHAR(50)    NOT NULL PRIMARY KEY,
    UserID      INT             NOT NULL,
    LastActive  DATETIME2       NOT NULL
);
GO

Exercise 3: Load 10,000 rows into each table and compare

-- Load into memory-optimized table and time it
DECLARE @start DATETIME2 = SYSUTCDATETIME();
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.SessionCache (SessionID, UserID, LastActive)
    VALUES (CAST(NEWID() AS NVARCHAR(50)), @i % 1000, SYSUTCDATETIME());
    SET @i += 1;
END;

SELECT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()) AS InMemoryMs;
GO

-- Load into disk-based table and time it
DECLARE @start DATETIME2 = SYSUTCDATETIME();
DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.SessionCache_Disk (SessionID, UserID, LastActive)
    VALUES (CAST(NEWID() AS NVARCHAR(50)), @i % 1000, SYSUTCDATETIME());
    SET @i += 1;
END;

SELECT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()) AS DiskMs;
GO

Exercise 4: Create and test a natively compiled procedure

CREATE PROCEDURE dbo.InsertSession_NC
    @SessionID  NVARCHAR(50),
    @UserID     INT
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    INSERT INTO dbo.SessionCache (SessionID, UserID, LastActive)
    VALUES (@SessionID, @UserID, SYSUTCDATETIME());
END;
GO

-- Run 1,000 executions and compare CPU time vs the interpreted version
DECLARE @start DATETIME2 = SYSUTCDATETIME();
DECLARE @i INT = 1;

WHILE @i <= 1000
BEGIN
    EXEC dbo.InsertSession_NC
        @SessionID = CAST(NEWID() AS NVARCHAR(50)),
        @UserID    = @i % 1000;
    SET @i += 1;
END;

SELECT DATEDIFF(MILLISECOND, @start, SYSUTCDATETIME()) AS NativeCompiledMs;
GO

Exercise 5: Monitor memory usage

SELECT
    memory_consumer_type_desc,
    object_name,
    allocated_bytes / 1024      AS AllocatedKB,
    used_bytes / 1024           AS UsedKB
FROM sys.dm_db_xtp_memory_consumers
ORDER BY allocated_bytes DESC;

Exercise 6: Test durability by restarting SQL Server

-- Before restart: count rows in both tables
SELECT 'InMemory' AS TableType, COUNT(*) AS Rows FROM dbo.SessionCache
UNION ALL
SELECT 'Disk',                  COUNT(*) AS Rows FROM dbo.SessionCache_Disk;

-- Restart SQL Server service
-- After restart: run the same query
-- SessionCache (SCHEMA_ONLY) will have 0 rows
-- SessionCache_Disk will retain all rows
-- This confirms the durability difference between SCHEMA_ONLY and disk tables

Exercise 7: Add a SCHEMA_AND_DATA durable table

-- Create a durable version with SCHEMA_AND_DATA
CREATE TABLE dbo.Orders_Durable
(
    OrderID     INT             NOT NULL
                PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000),
    CustomerID  INT             NOT NULL,
    OrderTotal  DECIMAL(18,2)   NOT NULL,
    CreatedOn   DATETIME2       NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- After restart this table retains its rows
-- Compare recovery time vs SessionCache_Disk for large row counts

References

• Microsoft Docs: In-Memory OLTP Overview
• Microsoft Docs: Requirements for Using In-Memory OLTP
• Microsoft Docs: Native Compilation of Tables and Stored Procedures
• Microsoft Docs: sys.dm_db_xtp_memory_consumers
• SQLYARD: DP-800 SQL AI Developer Associate Study Guide
• SQLYARD: SQL Server Performance Tuning Complete Guide
• SQLYARD: SQL Server Index Tuning Guide