In-Memory OLTP (Hekaton)

In-Memory OLTP, also known by its code name Hekaton, is a performance-enhancing feature in Microsoft SQL Server that
allows you to store and process tables entirely in system memory. Introduced in SQL Server 2014 and significantly enhanced in later releases, In-Memory OLTP is designed to remove the bottlenecks of disk I/O, locks, and latches typically associated with high-throughput transactional workloads.

The main goals of In-Memory OLTP are:

• Dramatically improve throughput for insert-heavy and update-heavy workloads.
• Eliminate locking and latching by using optimistic multi-version concurrency control (MVCC).
• Enable native compilation of stored procedures for faster execution.

Tables using In-Memory OLTP differ from traditional tables:

• They are created with the MEMORY_OPTIMIZED=ON option.
• They reside entirely in RAM but are durable—data is persisted to disk using a special checkpointing mechanism.
• They support non-durable (schema-only) tables as well for temporary caching needs.

In-Memory OLTP includes the concept of natively compiled stored procedures, which are compiled into machine code at deployment time. These procedures bypass the traditional SQL interpreter, offering lower CPU overhead and faster execution.

Common use cases include:

• High-volume session state management
• Order processing systems
• Real-time analytics on transactional data
• Queues or event stream ingestion

However, there are limitations to consider:

• Not all T-SQL features are supported in natively compiled procedures (especially older versions).
• Memory usage must be closely monitored to avoid running out of space.
• Additional care is required during backups and recovery due to separate checkpoint file pairs (CFP).

By leveraging In-Memory OLTP, SQL Server DBAs can dramatically reduce latency and increase throughput in transactional
systems, making it a valuable option for modern high-performance databases.