A client of mine had a session-state table taking 40,000 writes a minute during a flash sale, and every single one of them was queuing behind PAGELATCH_EX waits on the last page of a clustered index. Adding more CPU didn't help. Bigger disks didn't help. The bottleneck wasn't I/O, it was threads fighting over the same in-memory page structures. That's the exact scenario memory-optimized tables were built for, and it's also where I've seen people reach for them when the actual problem is a bad index design that In-Memory OLTP will just make expensive to fix.
What memory-optimized tables actually are
A memory-optimized table is a normal-looking CREATE TABLE with WITH (MEMORY_OPTIMIZED=ON) tacked on. Rows live in memory as the primary copy, not as a disk-page cache. There's no buffer pool, no page latching, no lock manager in the traditional sense. Concurrency is handled with row versioning and optimistic multi-version concurrency control, so readers never block writers and writers don't block each other on the same row unless they're actually touching the same row version.
You still get durability by default: a checkpoint file pair on disk and transaction log records mean a memory-optimized table survives a restart just like a regular one, unless you explicitly opt out with DURABILITY=SCHEMA_ONLY for things like staging tables or session state where losing the data on restart is fine.
The performance case according to Microsoft's own docs is specifically latch contention, lock contention, and CPU overhead from the interpreter, not "make my queries faster" in general. If your workload isn't hitting one of those three, this feature won't do much for you.
The how-to
Here's a durable memory-optimized table with a hash index for point lookups and a range index for the historical query pattern:
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) NOT NULL
PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL
INDEX ix_UserId NONCLUSTERED
HASH WITH (BUCKET_COUNT = 1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
And a non-durable session table where you're fine losing rows on a restart, which is the classic ASP.NET session state use case:
CREATE TABLE dbo.UserSession (
SessionId INT IDENTITY(1,1) NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 400000),
UserId INT NOT NULL,
CreatedDate DATETIME2 NOT NULL,
ShoppingCartId INT
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
Before you create the table, the database needs a memory-optimized filegroup:
ALTER DATABASE MyDb ADD FILEGROUP MyDb_mod CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE MyDb ADD FILE (
name = 'MyDb_mod1',
filename = 'C:\data\MyDb_mod1'
) TO FILEGROUP MyDb_mod;
Where the real speedup comes from is pairing this with a natively compiled stored procedure. Interpreted T-SQL against a memory-optimized table is already faster than against a disk-based table, but it's still going through the interpreter. A natively compiled proc gets compiled to machine code at creation time:
CREATE PROCEDURE dbo.usp_AssignCart @SessionId INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english'
)
DECLARE @UserId INT, @ShoppingCartId INT;
SELECT @UserId = UserId, @ShoppingCartId = ShoppingCartId
FROM dbo.UserSession
WHERE SessionId = @SessionId;
IF @UserId IS NULL
THROW 51000, N'Session or cart not found.', 1;
UPDATE dbo.UserSession
SET ShoppingCartId = @ShoppingCartId
WHERE SessionId = @SessionId;
END;
Notice the BEGIN ATOMIC block and the mandatory isolation level. That's not optional boilerplate, it's how the engine decides transaction semantics for native code, and errors like arithmetic overflow or divide-by-zero get caught at creation time instead of runtime, which trips people up the first time a CREATE PROCEDURE fails on a syntax-looking issue that's actually a data-type problem.
On indexes: use a hash index only when every query against it is an equality predicate hitting every key column, point lookups by session ID or order ID. Use a nonclustered (range) index for anything with ORDER BY, BETWEEN, or inequality comparisons. Getting this backwards is one of the more common mistakes, people default to hash indexes because they sound faster, then wonder why a range scan on that column is doing a full index scan instead.
Where this requires care
Edition and version gating matters. In-Memory OLTP has been available in every edition since SQL Server 2016 SP1, but Standard Edition caps you at 32 GB of memory-optimized data per database, and Express caps at 352 MB. If you're still on 2014 or 2016 RTM, it's Enterprise/Developer/Evaluation only. Check your edition before you architect around this.
Bucket count is a one-way decision, mostly. Hash indexes need a bucket count set at creation, and getting it too low creates chain collisions, too high wastes memory permanently since you can't easily shrink it without rebuilding. As of more recent SQL Server versions you can ALTER TABLE ... ALTER INDEX ... REBUILD WITH (BUCKET_COUNT = n), but plan for this rather than guessing and hoping.
T-SQL support inside natively compiled procs is a real subset. No MERGE, limited join types in older versions, no cursors in the way you're used to. You will hit a wall porting an existing complex proc over verbatim. Budget time to rewrite, not just recompile.
Foreign keys and CHECK constraints referencing a disk-based table from a memory-optimized one aren't supported, and vice versa in some directions depending on version. If your memory-optimized table needs referential integrity against a regular table, you're enforcing it in application code or triggers, not the schema.
Optimistic concurrency assumes low conflict rates. If two sessions are frequently trying to update the same row, you get conflict errors back to the app instead of blocking, and now your app needs retry logic it probably doesn't have. This is the one that bites people hardest: they migrate a hot table expecting free concurrency and instead get a wave of 41302/41305 errors under contention they didn't have with lock-based blocking.
Always Encrypted with secure enclaves doesn't work on memory-optimized columns, and CLR code can't touch memory-optimized tables on the same connection. Check both before you commit to a migration if either is in your stack.
My take
In-Memory OLTP is a scalpel, not a general performance upgrade. If your profiler shows PAGELATCH or LCK_M waits stacking up on a specific hot table under real concurrency, and the table's working set actually fits in memory, this is one of the few SQL Server features that delivers the order-of-magnitude gains the marketing promises. If your problem is a missing index, a bad execution plan, or tempdb spills, move memory-optimized tables to the bottom of the list, they add operational complexity (memory sizing, checkpoint files, a genuinely different T-SQL subset) that isn't worth paying for a problem they don't solve. I've only reached for it maybe three times in production over the years, and every time it was because the wait stats told me to, not because a table was "slow."
Top comments (0)