SQL Server In-Memory OLTP Configuration

🚀 SQL Server In-Memory OLTP: The Complete Configuration Guide

In-Memory OLTP (formerly known as "Hekaton") is SQL Server's revolutionary memory-optimized database engine. It can deliver 10x-30x performance improvements for OLTP workloads by eliminating disk I/O, reducing locking, and using natively compiled stored procedures. This comprehensive guide covers everything you need to implement In-Memory OLTP successfully in production environments.

📊 Understanding In-Memory OLTP Architecture

How In-Memory OLTP Works

Unlike traditional disk-based tables, memory-optimized tables reside entirely in memory. SQL Server uses optimistic multi-version concurrency control (MVCC) instead of locks, eliminating blocking for read operations.

In-Memory OLTP Components

+---------------------------+
|     SQL Server Engine     |
+---------------------------+
         |
+---------------------------+
|   Memory-Optimized        |
|   Data and Indexes        |
|   (Always in RAM)         |
+---------------------------+
         |
+---------------------------+
|   Checkpoint File Pairs   |
|   (Data + Delta Files)    |
|   For Durability          |
+---------------------------+

🔧 Configuring In-Memory OLTP

Step 1: Create Memory-Optimized Filegroup

-- Add memory-optimized filegroup to database
ALTER DATABASE AdventureWorks
ADD FILEGROUP InMemory_FG CONTAINS MEMORY_OPTIMIZED_DATA;

-- Add container (directory) to filegroup
ALTER DATABASE AdventureWorks
ADD FILE (
    NAME = 'InMemory_Container',
    FILENAME = 'D:\SQLData\InMemory\AdventureWorks_InMem'
)
TO FILEGROUP InMemory_FG;

Step 2: Configure Memory Settings

-- Check current memory configuration
SELECT
    name,
    value_in_use,
    description
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)'
);

-- Set memory limits considering In-Memory OLTP
-- Rule of thumb: Reserve 2x the data size for In-Memory
EXEC sp_configure 'max server memory (MB)', 65536;  -- 64 GB
RECONFIGURE;

-- Check memory-optimized data memory usage
SELECT
    type,
    memory_node_id,
    pages_kb / 1024 AS memory_mb,
    virtual_memory_committed_kb / 1024 AS committed_mb
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%XTP%'
ORDER BY pages_kb DESC;

Step 3: Enable Memory-Optimized Features

-- Set database to allow memory-optimized tables
ALTER DATABASE AdventureWorks
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

-- Enable native compilation (if not already)
-- This is enabled by default in SQL Server 2016+

📊 Creating Memory-Optimized Tables

Basic Memory-Optimized Table

-- Create memory-optimized table with durability
CREATE TABLE Sales.OrdersInMemory
(
    OrderID INT NOT NULL
        CONSTRAINT PK_OrdersInMemory PRIMARY KEY NONCLUSTERED,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status TINYINT NOT NULL DEFAULT 1,

    -- Hash index for point lookups
    INDEX IX_CustomerID HASH (CustomerID) WITH (BUCKET_COUNT = 1000000),

    -- Range index for date queries
    INDEX IX_OrderDate NONCLUSTERED (OrderDate)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA  -- Survives restart
);

Non-Durable Table (Schema Only)

-- Non-durable table for temporary/session data
CREATE TABLE dbo.SessionCache
(
    SessionID UNIQUEIDENTIFIER NOT NULL
        CONSTRAINT PK_SessionCache PRIMARY KEY NONCLUSTERED
        HASH WITH (BUCKET_COUNT = 100000),
    UserID INT NOT NULL,
    Data NVARCHAR(4000) NOT NULL,
    CreatedDate DATETIME2 NOT NULL DEFAULT SYSDATETIME(),
    ExpiresDate DATETIME2 NOT NULL,

    INDEX IX_UserID HASH (UserID) WITH (BUCKET_COUNT = 50000),
    INDEX IX_Expires NONCLUSTERED (ExpiresDate)
)
WITH (
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_ONLY  -- Data lost on restart, but faster
);

Choosing Index Types

-- Hash index: Perfect for exact matches
SELECT * FROM Sales.OrdersInMemory WHERE CustomerID = 12345;

-- Range index: Perfect for range queries
SELECT * FROM Sales.OrdersInMemory
WHERE OrderDate BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY OrderDate;

⚡ Natively Compiled Stored Procedures

Creating Native Procedures

Native procedures compile T-SQL to machine code for maximum performance.

-- Natively compiled stored procedure
CREATE PROCEDURE Sales.InsertOrderNative
(
    @CustomerID INT,
    @TotalAmount DECIMAL(18,2),
    @OrderID INT OUTPUT
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    INSERT INTO Sales.OrdersInMemory (OrderDate, CustomerID, TotalAmount, Status)
    VALUES (SYSDATETIME(), @CustomerID, @TotalAmount, 1);

    SET @OrderID = SCOPE_IDENTITY();
END;
GO

-- High-performance order lookup
CREATE PROCEDURE Sales.GetCustomerOrdersNative
(
    @CustomerID INT,
    @StartDate DATETIME2,
    @EndDate DATETIME2
)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
    SELECT OrderID, OrderDate, TotalAmount, Status
    FROM Sales.OrdersInMemory
    WHERE CustomerID = @CustomerID
      AND OrderDate >= @StartDate
      AND OrderDate <= @EndDate
    ORDER BY OrderDate DESC;
END;
GO

Native Procedure Limitations

⚠️ Warning: Native procedures have restrictions. Not all T-SQL features are supported.

💡 Migrating Tables to In-Memory

Memory Optimization Advisor

-- Analyze table for In-Memory compatibility
EXEC sys.sp_table_column_type_check
    @schema_name = 'Sales',
    @object_name = 'Orders';

-- Generate migration script
-- Use SSMS: Right-click table > Memory Optimization Advisor

Migration Script Pattern

-- Step 1: Create memory-optimized table
CREATE TABLE Sales.Orders_InMemory
(
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    OrderDate DATETIME2 NOT NULL,
    CustomerID INT NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status TINYINT NOT NULL,

    INDEX IX_CustomerID HASH (CustomerID) WITH (BUCKET_COUNT = 500000),
    INDEX IX_OrderDate NONCLUSTERED (OrderDate)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Step 2: Copy data
INSERT INTO Sales.Orders_InMemory
SELECT OrderID, OrderDate, CustomerID, TotalAmount, Status
FROM Sales.Orders;

-- Step 3: Rename tables (in transaction for atomicity)
BEGIN TRANSACTION;
    EXEC sp_rename 'Sales.Orders', 'Orders_Archive';
    EXEC sp_rename 'Sales.Orders_InMemory', 'Orders';
COMMIT;

-- Step 4: Recreate views, procedures, etc.

📈 Performance Monitoring

Memory Usage Monitoring

-- Overall memory-optimized memory consumption
SELECT
    object_name,
    type_desc,
    memory_consumer_type_desc,
    allocated_bytes / 1024 / 1024 AS allocated_mb,
    used_bytes / 1024 / 1024 AS used_mb
FROM sys.dm_db_xtp_memory_consumers
ORDER BY allocated_bytes DESC;

-- Memory usage by table
SELECT
    OBJECT_SCHEMA_NAME(t.object_id) AS SchemaName,
    OBJECT_NAME(t.object_id) AS TableName,
    i.name AS IndexName,
    ms.memory_allocated_for_table_kb / 1024 AS table_mb,
    ms.memory_allocated_for_indexes_kb / 1024 AS index_mb,
    ms.memory_used_by_table_kb / 1024 AS table_used_mb,
    ms.memory_used_by_indexes_kb / 1024 AS index_used_mb
FROM sys.dm_db_xtp_table_memory_stats ms
INNER JOIN sys.tables t ON ms.object_id = t.object_id
LEFT JOIN sys.indexes i ON t.object_id = i.object_id AND i.index_id = 1
ORDER BY ms.memory_allocated_for_table_kb DESC;

Index Usage and Hash Collisions

-- Check hash index bucket utilization
SELECT
    OBJECT_SCHEMA_NAME(hs.object_id) AS SchemaName,
    OBJECT_NAME(hs.object_id) AS TableName,
    i.name AS IndexName,
    hs.total_bucket_count,
    hs.empty_bucket_count,
    hs.avg_chain_length,
    hs.max_chain_length,
    CAST((hs.total_bucket_count - hs.empty_bucket_count) * 100.0
         / hs.total_bucket_count AS DECIMAL(5,2)) AS bucket_utilization_pct
FROM sys.dm_db_xtp_hash_index_stats hs
INNER JOIN sys.indexes i ON hs.object_id = i.object_id AND hs.index_id = i.index_id
ORDER BY hs.avg_chain_length DESC;

-- Ideal: avg_chain_length = 1, bucket_utilization 30-70%
-- High avg_chain_length = too few buckets (increase BUCKET_COUNT)
-- Low bucket_utilization = too many buckets (waste of memory)

Native Procedure Performance

-- Native procedure execution statistics
SELECT
    OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
    OBJECT_NAME(object_id) AS ProcedureName,
    execution_count,
    total_worker_time / 1000 AS total_cpu_ms,
    total_worker_time / execution_count / 1000 AS avg_cpu_ms,
    total_elapsed_time / 1000 AS total_time_ms,
    total_elapsed_time / execution_count / 1000 AS avg_time_ms,
    cached_time,
    last_execution_time
FROM sys.dm_exec_procedure_stats
WHERE object_id IN (
    SELECT object_id FROM sys.sql_modules WHERE uses_native_compilation = 1
)
ORDER BY total_worker_time DESC;

🔧 Optimizing Hash Bucket Count

Calculate Optimal Bucket Count

-- Estimate bucket count for existing table
DECLARE @DistinctValues BIGINT;
DECLARE @ExpectedGrowth FLOAT = 1.5;  -- 50% growth buffer

SELECT @DistinctValues = COUNT(DISTINCT CustomerID)
FROM Sales.Orders;

SELECT
    @DistinctValues AS CurrentDistinctValues,
    CAST(@DistinctValues * @ExpectedGrowth AS BIGINT) AS RecommendedBucketCount,
    POWER(2, CEILING(LOG(CAST(@DistinctValues * @ExpectedGrowth AS FLOAT)) / LOG(2))) AS RoundedToPowerOf2;

-- Bucket count should be power of 2 for optimal hashing

Rebuild Hash Index

-- Cannot ALTER hash bucket count - must recreate table
-- Use this pattern for large tables:

-- Step 1: Create new table with correct bucket count
CREATE TABLE Sales.OrdersInMemory_New
(
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2 NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    Status TINYINT NOT NULL,

    INDEX IX_CustomerID HASH (CustomerID) WITH (BUCKET_COUNT = 2097152)  -- 2^21
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

-- Step 2: Copy data using native proc for speed
INSERT INTO Sales.OrdersInMemory_New
SELECT * FROM Sales.OrdersInMemory;

-- Step 3: Swap tables
BEGIN TRANSACTION;
    DROP TABLE Sales.OrdersInMemory;
    EXEC sp_rename 'Sales.OrdersInMemory_New', 'OrdersInMemory';
COMMIT;

✅ Best Practices

When to Use In-Memory OLTP

When NOT to Use In-Memory

Configuration Checklist

• [ ] Create memory-optimized filegroup on fast storage
• [ ] Configure appropriate max server memory
• [ ] Calculate correct hash bucket counts
• [ ] Use native procedures for critical paths
• [ ] Monitor memory consumption regularly
• [ ] Test failover/recovery scenarios
• [ ] Plan for memory growth

💡 Pro Tip: Start with DURABILITY = SCHEMA_ONLY tables for caching scenarios. The performance gain is significant, and data loss on restart is acceptable for cache data.

🔧 Troubleshooting

Out of Memory Errors

-- Check memory pressure
SELECT
    type,
    pages_kb / 1024 AS memory_mb,
    page_size_in_bytes,
    virtual_memory_reserved_kb / 1024 AS reserved_mb,
    virtual_memory_committed_kb / 1024 AS committed_mb
FROM sys.dm_os_memory_clerks
WHERE type LIKE '%XTP%'
ORDER BY pages_kb DESC;

-- Check resource pool limits
SELECT
    pool_id,
    name,
    min_memory_percent,
    max_memory_percent,
    used_memory_kb / 1024 AS used_mb,
    target_memory_kb / 1024 AS target_mb
FROM sys.dm_resource_governor_resource_pools;

Checkpoint Performance

-- Monitor checkpoint file operations
SELECT
    container_id,
    checkpoint_file_id,
    file_type_desc,
    state_desc,
    file_size_in_bytes / 1024 / 1024 AS size_mb,
    file_size_used_in_bytes / 1024 / 1024 AS used_mb
FROM sys.dm_db_xtp_checkpoint_files
ORDER BY file_size_in_bytes DESC;

-- Merge progress
SELECT
    lower_bound_tsn,
    upper_bound_tsn,
    is_active,
    items_count,
    inserted_item_count,
    deleted_item_count
FROM sys.dm_db_xtp_merge_requests;

Conclusion

In-Memory OLTP can deliver transformative performance improvements for the right workloads. Key takeaways:

• Prepare infrastructure - Memory-optimized filegroup and adequate RAM
• Choose indexes wisely - Hash for point lookups, range for scans
• Use native procedures - Maximum performance for critical paths
• Monitor memory usage - Data must fit in available memory
• Right-size bucket counts - Critical for hash index performance

With proper implementation, In-Memory OLTP can reduce latency from milliseconds to microseconds.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

• SQL Server Memory Management Best Practices
• SQL Server Performance Tuning Guide
• SQL Server TempDB Configuration

Need help with SQL Server optimization? Contact us for expert database consulting.