A Complete Guide from Novice to Expert

• Full Hands-On Workshop**

SQL Server’s In-Memory OLTP engine (also called “Hekaton”) is one of the most powerful performance features in the platform. It allows you to create memory-optimized tables, natively compiled stored procedures, and high-throughput transactional workloads.

This guide explains what in-memory tables are, when to use them, how to set them up, how to test them, how to measure performance gains, and includes a complete workshop to practice everything step by step.

---

1. What Are In-Memory Tables?

In-memory tables are database tables stored entirely in memory, not on disk. They use special lock-free data structures that allow extremely fast access with minimal blocking or latching.

Key properties:

• Rows live in memory for ultra-low latency
• Disk storage is used only for durability (if enabled)
• No page/extent structures like traditional tables
• Lock-free architecture (no latches, locks, or spinlocks during access)
• Optimized for high transaction throughput

Why SQL Server created this feature:

• Traditional disk-backed tables hit bottlenecks under heavy OLTP loads
• In-memory tables bypass locking and latching
• Native compilation reduces CPU and query duration dramatically

---

2. When Should You Use In-Memory Tables?

Good Use Cases:

1. High-frequency OLTP operations
   (queues, order processing, chat messages, telemetry, gaming events, leaderboards)
2. High contention (many concurrent writes)
   Perfect for scenarios where locking slows performance.
3. Session state tables
   Web apps, gaming servers, and microservices.
4. Staging tables in ETL processes
5. Cache-like reference tables
   Lookups, configuration data, pricing rates.
6. High-speed ingestion
   IoT, finance ticks, event logs.

Not Good For:

• Large historical data
• Reporting workloads
• Wide tables with large LOB columns
• Tables requiring FULLTEXT or replication in versions where it’s not supported
• Memory-heavy workloads when you do not have enough RAM

---

3. Requirements to Use In-Memory OLTP

SQL Server Edition:

• Available in SQL Server 2014+
• Best experience in SQL Server 2016+ (much more stable)

Memory:

• Sufficient RAM to hold the entire memory-optimized table

Filegroup:

You must create a MEMORY_OPTIMIZED_DATA filegroup.

---

**4. Setting Up In-Memory Tables

(Novice to Expert Step-By-Step)**

Step 1: Create the Memory-Optimized Filegroup

ALTER DATABASE MyDB 
ADD FILEGROUP MyDB_mod 
CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE MyDB 
ADD FILE (NAME='MyDB_mod1', FILENAME='C:\Data\MyDB_mod1') 
TO FILEGROUP MyDB_mod;
GO

Step 2: Create Your First Memory-Optimized Table

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);

Durability Options:

• SCHEMA_AND_DATA = persists to disk
• SCHEMA_ONLY = disappears on restart (fastest, good for staging tables)

---

Step 3: Insert and Query Normally

INSERT INTO dbo.Orders_InMemory (OrderID, CustomerID, OrderTotal, CreatedOn)
VALUES (1, 42, 199.99, SYSUTCDATETIME());

SELECT * FROM dbo.Orders_InMemory;

**5. Natively Compiled Stored Procedures

(Optional but extremely powerful)**

CREATE PROCEDURE dbo.InsertOrder_InMemory
    @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
    VALUES (@OrderID, @CustomerID, @OrderTotal, SYSUTCDATETIME());
END;

Native compilation yields massive performance gains by compiling straight to machine code.

---

6. Benefits of In-Memory Tables

Performance Gains:

• 10x+ faster inserts/updates
• Minimal CPU cost
• Eliminates latch contention
• No lock manager overhead
• Highly scalable concurrency

Developer Productivity:

• Query them like normal tables
• All T-SQL features supported in latest releases
• Great for performance-critical parts of the application

Flexibility:

• Multiple durability modes
• Good for caching patterns
• Great for message queues and leaderboards

---

7. Limitations You Must Understand

Memory usage is critical

If you run out of memory, inserts fail.

Not all data types allowed (older versions)

Modern versions support most types except LOB-heavy columns.

No offline rebuilds

Memory-optimized tables behave differently under backup/restore.

Hash indexes must be sized correctly

Too small = many collisions
Too large = wasted memory

Choose BUCKET_COUNT close to number of rows.

---

8. Performance Testing in Lower Environments

Before using In-Memory OLTP in production:

Test How Fast Inserts/Updates Improve

Measure baseline:

SET STATISTICS TIME ON;
INSERT INTO dbo.Orders_SQL (columns…) VALUES (…);

Then compare:

INSERT INTO dbo.Orders_InMemory VALUES (…);

Track Performance Gains

Good metrics:

• Average write latency
• CPU consumption per transaction
• Lock waits (should drop dramatically)
• Batch Requests/sec
• P99 response time

Use:

SELECT * FROM sys.dm_db_xtp_transaction_stats;
SELECT * FROM sys.dm_xtp_memory_consumers;

**9. Performance Loss Scenarios

(What to watch for)**

You may hit performance degradation if:

1. Memory fills up
2. Hash bucket count too small (leads to collisions)
3. Durability = SCHEMA_AND_DATA on very high write rates
4. Tempdb pressure with too many versions
5. Incorrect workload choice

Always test under realistic concurrency.

---

**10. Step-By-Step Workshop

(Hands-On Training for Novice → Expert)**

Use this full workshop in a lab environment.

---

Exercise 1: Create a Memory-Optimized Filegroup

ALTER DATABASE LabDB 
ADD FILEGROUP LabDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE LabDB 
ADD FILE (NAME='LabDB_mod1', FILENAME='D:\LabDB_mod1') 
TO FILEGROUP LabDB_mod;

Exercise 2: Create Your First Memory-Optimized Table

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);

Goal: Understand volatile in-memory tables.

---

Exercise 3: Load 10,000 Rows and Measure Speed

DECLARE @i INT = 1;

WHILE @i <= 10000
BEGIN
    INSERT INTO dbo.SessionCache VALUES (NEWID(), RAND()*1000, SYSUTCDATETIME());
    SET @i += 1;
END;

Record execution time.

---

Exercise 4: Compare Against a Standard Disk Table

SELECT COUNT(*) FROM dbo.SessionCache;

vs

SELECT COUNT(*) FROM dbo.SessionCache_Disk;

Measure latency.

---

Exercise 5: Create 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)
    INSERT INTO dbo.SessionCache
    VALUES (@SessionID, @UserID, SYSUTCDATETIME());
END;

Run it in a loop and compare CPU usage.

---

Exercise 6: Track Memory Usage of In-Memory Objects

SELECT * FROM sys.dm_db_xtp_memory_consumers;

Exercise 7: Test Durability Options

Create one table with:

• SCHEMA_ONLY (fastest, not durable)
• SCHEMA_AND_DATA (durable)

Restart SQL Server and compare which table retained rows.

---

Exercise 8: Compare Wait Stats Before and After

Traditional wait stats:

SELECT * FROM sys.dm_os_wait_stats;

In-memory optimized waits:

SELECT * FROM sys.dm_db_xtp_transaction_stats;

---

11. Final Thoughts

Memory-optimized tables can drastically improve performance when used correctly. They shine in high-throughput, low-latency OLTP workloads. They remove locking, reduce CPU, and provide speed that disk-based tables cannot match.

But they require planning—especially around memory and durability—and careful testing in lower environments.

Used correctly, In-Memory OLTP can be the biggest performance boost you will ever apply to a SQL Server instance.

---

Inline References

Microsoft In-Memory OLTP Overview
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/in-memory-oltp-in-memory-optimization

Memory-Optimized Table Requirements
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/requirements-for-using-in-memory-oltp

Native Compiled Stored Procedures
https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/native-compiled-stored-procedures

Memory Consumers DMV
https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-xtp-memory-consumers-transact-sql