In-Memory OLTP in SQL Server: When Memory-Optimized Tables Actually Help

This is the direct architectural answer to Module 5’s latch contention problem — tables that avoid locks and latches almost entirely, at the cost of real constraints on how you use them.

Creating a Memory-Optimized Table

-- Requires a MEMORY_OPTIMIZED_DATA filegroup on the database first
CREATE TABLE dbo.SessionState (
    session_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED,
    user_id INT NOT NULL,
    last_activity DATETIME2 NOT NULL,
    INDEX IX_UserId NONCLUSTERED (user_id)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Why It Avoids the Contention From Module 5

Natively Compiled Procedures: The Other Half

CREATE PROCEDURE dbo.usp_UpdateSessionActivity
    @session_id UNIQUEIDENTIFIER
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
    UPDATE dbo.SessionState SET last_activity = SYSDATETIME() WHERE session_id = @session_id;
END;

Natively compiled procedures are compiled to actual machine code, not interpreted T-SQL — the performance ceiling is dramatically higher, but the T-SQL surface area supported inside them is deliberately restricted (no dynamic SQL, limited function support).

When This Is (and Isn’t) the Right Tool

In-Memory OLTP genuinely shines for extreme-throughput, high-contention scenarios like session state, real-time bidding, or IoT ingestion — exactly the ever-increasing-key latch contention pattern from Module 5. It’s a poor fit for general-purpose reporting tables or anything needing the full T-SQL surface (complex constraints, most trigger types). Reach for it only after confirming, with evidence, that lock/latch contention is the actual bottleneck — not as a default upgrade.