How SQL Server Hekaton Supercharges OLTP with In‑Memory, Latch‑Free Architecture

Problems in Traditional Relational Engines

Classic OLTP engines suffer from three fundamental bottlenecks that become severe on modern multi‑core CPUs, large memory pools, and fast SSDs:

Latch‑protected in‑memory structures create hot‑spots. When many threads need to read or modify the same data page, a latch forces all but one thread to wait, dramatically reducing concurrency.

Lock‑based MVCC introduces blocking and dead‑locks. Two concurrent updates on the same row must acquire exclusive locks; if they conflict, one transaction is blocked or aborted.

Interpretive execution plans incur high CPU overhead. The engine parses a logical plan, generates an interpreted operator tree, and invokes many function calls for each row, preventing the CPU from staying in a tight execution pipeline.

Figure 1 illustrates latch contention: query Q1 holds a latch on page 7 while Q2 is forced to wait, creating a hot‑spot.

SQL Server In‑Memory OLTP (Hekaton) Architecture

Hekaton integrates a memory‑optimized engine into SQL Server while keeping the traditional engine for on‑disk tables. Memory‑optimized tables are stored in a latch‑free B‑tree (Bw‑tree) structure, eliminating latch hot‑spots.

Optimistic concurrency control replaces locks with timestamps:

Each transaction receives a global Begin_TS at start.

Every row version stores Begin_TS and End_TS (initially +∞).

During an update a new version is created; the old version’s End_TS is set to the updating transaction’s ID.

At commit the engine validates that the transaction’s view (its Begin_TS) is still consistent with the chosen isolation level (Snapshot, Repeatable Read, Serializable). No locks are taken.

Lock‑free updates use Compare‑and‑Swap (CAS): the engine atomically swaps the physical address of a row only if the expected value matches, otherwise the operation fails and must be retried.

Updates are recorded as delta‑updates in a mapping table that points to the latest physical address of a page. When a page’s update chain grows, Hekaton periodically compacts the chain into a new page and releases the old pages as garbage.

Native compilation transforms a logical query plan into C code, compiles it into a DLL, and executes the resulting machine code directly. This removes the interpretive layer and allows the CPU to stay in a tight execution pipeline.

Transaction Processing Model

Hekaton splits a transaction into three phases:

Execution : DML statements read the appropriate row versions based on the transaction’s Begin_TS and create new versions for updates.

Validation : Before commit the engine checks that the transaction’s view has not been invalidated by concurrent updates, according to the selected isolation level.

Snapshot: no validation needed.

Repeatable Read: re‑read rows touched during the transaction; abort if they changed.

Serializable: ensure the entire read set is unchanged.

Commit : The transaction writes a commit log record, replaces the temporary transaction IDs in the version rows with the final End_TS, and makes the new versions visible to other transactions.

Version visibility follows the rule Begin_TS ≤ transaction_TS < End_TS. Old versions whose End_TS is less than the oldest active transaction can be reclaimed, freeing memory.

Performance Benchmarks

Microsoft’s internal benchmarks compare native‑compiled, memory‑optimized queries against interpreted, on‑disk queries:

Random look‑up of 10 million rows on a memory‑optimized table is up to 17× faster than the same query on a traditional table.

CPU instruction count for a single‑row lookup drops from millions (interpreted) to a few hundred thousand (native‑compiled).

Update throughput improves dramatically when logging is disabled, because updates are performed lock‑free and in‑place.

Use‑Case Considerations

Hekaton delivers the greatest gains for workloads that are:

Read‑heavy or write‑light, where contention on rows is low.

Latency‑sensitive, benefiting from latch‑free access and native code execution.

Scenarios with high update conflict rates may experience frequent transaction aborts because the optimistic validation will fail. Additionally, because all data resides in memory, sufficient RAM must be provisioned; otherwise the engine will fall back to on‑disk storage, losing the performance advantage.

When logging is required for durability, Hekaton still writes to the transaction log, but the log can be configured to contain only schema changes for transient data (e.g., game state), further reducing I/O overhead.

Conclusion

SQL Server In‑Memory OLTP (Hekaton) removes latch contention, replaces lock‑based MVCC with timestamp‑based optimistic concurrency, and accelerates query execution through native compilation. These techniques can provide order‑of‑magnitude performance improvements for suitable workloads, but they require careful assessment of conflict patterns and memory capacity before adoption.