powershelldba.de

Memory-Optimized Tables: In-Memory OLTP in SQL Server

Store tables entirely in RAM for sub-millisecond queries. But memory-optimized tables have strict rules and surprising limitations. Here's what you need to know.

What is In-Memory OLTP?

In-Memory OLTP (Hekaton) is SQL Server's technology for storing tables entirely in RAM instead of on disk. This eliminates disk I/O and enables:

Introduced in SQL Server 2014, it's been refined through 2016, 2017, 2019, and beyond. But it's not a magic bullet — it has tradeoffs.

How It Works

The Architecture

Memory-optimized tables live entirely in RAM:

Traditional Table         Memory-Optimized Table
├─ Buffer Pool (RAM)       ├─ In-Memory Storage
├─ Disk (SSD/HDD)          └─ Transaction Log (disk for durability)
└─ Transaction Log

Access path:
1. Request arrives
2. Check buffer pool (cache hit/miss)
3. If miss, read from disk
4. Execute query
5. Write to log

With memory-optimized:
1. Request arrives
2. Read from RAM (always in memory)
3. Execute query
4. Write to log

No disk reads except on startup/recovery!

Durability: How Data Survives Server Crashes

Even though data lives in RAM, it's durable. SQL Server writes transaction logs to disk (checkpoint files):

On server restart, SQL Server rebuilds the table in RAM from these files. The process is fast (seconds, not hours) but not instant.

Creating Memory-Optimized Tables

Basic Syntax

-- Step 1: Create a filegroup for memory-optimized data
ALTER DATABASE MyDatabase
ADD FILEGROUP MemOptFileGroup CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE MyDatabase
ADD FILE (
    NAME = MemOptData,
    FILENAME = 'C:\SQLData\MemOptData'
) TO FILEGROUP MemOptFileGroup;

-- Step 2: Create memory-optimized table
CREATE TABLE OrdersOptimized (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    Amount DECIMAL(10,2)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Durability options:
-- SCHEMA_AND_DATA: Durable (default)
-- SCHEMA_ONLY: Data lost on restart (for temporary working sets)

Indexing Memory-Optimized Tables

Memory-optimized tables support two index types:

CREATE TABLE OrdersOptimized (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    INDEX idx_customer HASH (CustomerID) WITH (BUCKET_COUNT = 100000),
    INDEX idx_date RANGE (OrderDate)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Limitations: The Catch

Significant Restrictions

Memory-optimized tables have strict limitations (especially in earlier versions, relaxed in SQL Server 2019+):

Feature Traditional Table Memory-Optimized Table
Data Types All supported Limited (no TEXT, BLOB; VARCHAR max 8000 bytes)
Triggers AFTER/INSTEAD OF AFTER only, limited functionality
Foreign Keys Yes Partial support
Constraints All types PRIMARY KEY, UNIQUE only
Transactions ACID with locking ACID with optimistic concurrency
Schema Changes Online (mostly) Limited (often requires table recreation)

No Blocking, But Conflicts

Memory-optimized tables use optimistic concurrency instead of locking:

Result: No deadlocks, but transient failures possible if high contention.

Performance: The Reality

Typical Gains

Performance depends on your workload:

Real Example: Trading System

-- Traditional table
INSERT INTO Trades (TradeID, SecurityID, Quantity, Price)
VALUES (@ID, @Security, @Qty, @Price);
-- Time: 5-10 ms per insert (disk I/O)
-- Throughput: 100-200 inserts/second

-- Memory-optimized table
INSERT INTO TradesOptimized (TradeID, SecurityID, Quantity, Price)
VALUES (@ID, @Security, @Qty, @Price);
-- Time: 0.05-0.1 ms per insert (no disk I/O)
-- Throughput: 10,000+ inserts/second
-- 50-100x faster!

When to Use Memory-Optimized Tables

Good Candidates

Poor Candidates

Common Pitfalls

Pitfall 1: Ignoring Memory Requirements

-- Table size calculation:
-- Each row uses: (column sizes) + 24 bytes overhead + index overhead
-- For 1 million rows with 100 bytes/row:
-- Minimum RAM: 1,000,000 * (100 + 24) + index overhead ≈ 150 MB

-- But SQL Server reserves 2x for GC overhead and concurrency:
-- Actual RAM consumed: ~300 MB

-- If you have multiple memory-optimized tables and other workloads,
-- RAM can fill up quickly. Plan accordingly.

Pitfall 2: Retrying Logic Missing

Memory-optimized tables use optimistic concurrency. Under contention, transactions can fail:

-- Without retry logic, transactions fail occasionally
INSERT INTO OrdersOptimized VALUES (...);  -- May fail with conflict

-- Correct: Implement retry logic
DECLARE @RetryCount INT = 0;
WHILE @RetryCount < 5
BEGIN
    BEGIN TRY
        INSERT INTO OrdersOptimized VALUES (...);
        BREAK;  -- Success
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 41302  -- Conflict detected
            SET @RetryCount = @RetryCount + 1;
        ELSE
            THROW;
    END CATCH;
END;

Pitfall 3: Not Monitoring Memory Usage

If memory-optimized tables consume all server RAM, other workloads suffer:

-- Monitor memory consumption
SELECT
    object_name,
    memory_allocated_for_table_kb,
    memory_used_by_table_kb
FROM sys.dm_db_xtp_table_memory_stats
ORDER BY memory_allocated_for_table_kb DESC;

Migration Strategy

Hybrid Approach

You don't need to migrate entire tables. Run hot data in memory-optimized, cold data in traditional tables:

-- Memory-optimized: Recent orders (last 7 days)
CREATE TABLE OrdersHot (...)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Traditional: Archive (older than 7 days)
CREATE TABLE OrdersArchive (...);

-- Nightly job moves old orders from Hot to Archive
DELETE FROM OrdersHot WHERE OrderDate < DATEADD(DAY, -7, GETDATE());
INSERT INTO OrdersArchive SELECT * FROM OrdersHot WHERE OrderDate < ...;

Steps to Migrate

  1. Identify hotspots (high contention, latency-critical tables)
  2. Test memory-optimized version on staging with production-like load
  3. Implement retry logic in application code
  4. Monitor memory consumption during pilot
  5. Gradually migrate, measuring performance gains
  6. Keep traditional tables for compatibility if needed

Monitoring Memory-Optimized Tables

-- Check memory usage
SELECT
    OBJECT_NAME(object_id) AS TableName,
    memory_allocated_for_table_kb / 1024 AS AllocatedMB,
    memory_used_by_table_kb / 1024 AS UsedMB
FROM sys.dm_db_xtp_table_memory_stats
ORDER BY memory_used_by_table_kb DESC;

-- Check transaction conflicts
SELECT
    object_name,
    total_access_count,
    total_conflict_count,
    CAST(100.0 * total_conflict_count / NULLIF(total_access_count, 0) AS DECIMAL(5,2)) AS conflict_pct
FROM sys.dm_db_xtp_nonclustered_index_stats
WHERE total_conflict_count > 0;

The Verdict

Memory-optimized tables are powerful for OLTP workloads with extreme performance requirements. But they're not a universal solution — they have strict limitations and require application-level retry logic. Use them surgically for hotspots, not wholesale.

Consider memory-optimized tables if:

Skip memory-optimized if:

← Back to Blog