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 associated with 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 restarts through checkpoint files and the transaction log, or non-durable, where data is lost on restart and is suitable for cache or staging scenarios.
- Key limitations include: A limited T-SQL surface area 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: This helps determine whether tables are good candidates for migration to In-Memory OLTP.
- Related terms: Buffer Pool, OLTP, Latch Wait, Concurrency, Memory-Optimized Tables.
