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.
Enjoyed this?
Subscribe to get every new lesson as soon as it’s published, and share it with a developer who’d find it useful.
📡 Subscribe via RSS |
Share on X |
Share on LinkedIn |
Share on Facebook
Want the full structured course with quizzes and hands-on labs? Check out SQL Server Performance Tuning, coming soon on this site.