SQL Server’s In-Memory OLTP engine (originally codenamed Hekaton) that stores tables and executes stored procedures entirely in memory using a lock-free, latch-free architecture, delivering extreme throughput for high-frequency OLTP workloads.
- Memory-Optimized Tables store all data in RAM using a multi-version concurrency control (MVCC) model: This eliminates lock and latch contention that limits traditional disk-based tables.
- Natively Compiled Stored Procedures are compiled to machine code at creation time: They execute without the interpretation overhead of interpreted T-SQL.
- In-Memory OLTP delivers the greatest gains for workloads characterized by: High concurrent inserts, point lookups, short transactions, and severe locking contention.
- Data durability options are available for Memory-Optimized Tables: Tables can be configured as durable, where data survives server restarts through checkpoint files and the transaction log, or as non-durable, where data is lost on restart and is intended for cache or staging use cases.
- Key limitations include: Limited T-SQL support in natively compiled procedures, lack of
ALTER TABLEsupport (requiring table recreation), and memory requirements that must be carefully planned. - DBAs should profile workloads using the AMR (Analysis, Migrate, Report) tool before migrating tables to In-Memory OLTP: This helps determine whether workloads are suitable candidates for migration.
- Related terms: Buffer Pool, OLTP, Latch Wait, Concurrency, Memory-Optimized Tables.
