Part 155: SQL Server In-Memory OLTP & Memory-Optimized Tables

Achieving extreme transaction throughput in Microsoft SQL Server without disk locks.

1. What is In-Memory OLTP (Hekaton)?

Introduced in SQL Server to eliminate latch and lock contention for high-concurrency workloads: - Data rows live completely in memory. - Uses lock-free and latch-free data structures (Bw-Tree and lock-free hash indexes). - Readers and writers never block each other.

2. Creating a Memory-Optimized Table

-- 1. Database must have a MEMORY_OPTIMIZED_DATA filegroup
ALTER DATABASE ShopDB ADD FILEGROUP ShopDB_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE ShopDB ADD FILE (NAME='ShopDB_mod_dir', FILENAME='D:\data\mod') TO FILEGROUP ShopDB_mod;

-- 2. Create memory-optimized table
CREATE TABLE dbo.SessionCache (
    SessionId UNIQUEIDENTIFIER NOT NULL,
    UserId INT NOT NULL,
    LastActive DATETIME2 NOT NULL,
    Payload NVARCHAR(MAX),
    CONSTRAINT PK_SessionCache PRIMARY KEY NONCLUSTERED HASH (SessionId) 
        WITH (BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

3. Natively Compiled Stored Procedures

SQL Server compiles T-SQL stored procedures directly into machine code (C DLLs):

CREATE PROCEDURE dbo.sp_UpdateSession
    @SessionId UNIQUEIDENTIFIER,
    @UserId INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (
    TRANSACTION ISOLATION LEVEL = SNAPSHOT, 
    LANGUAGE = N'us_english'
)
    UPDATE dbo.SessionCache 
    SET LastActive = SYSDATETIME() 
    WHERE SessionId = @SessionId;
END;

Key Takeaways

• Natively compiled procedures execute up to 30x faster than interpreted T-SQL.
• Ideal for high-frequency state updates, IoT sensor ingestion, and shopping cart sessions.

In Part 156, we explore SQL Server Columnstore Indexes for Analytical Workloads!