When In-Memory OLTP Helps and When It Does Not

In-Memory OLTP can help when transaction processing spends too much time coordinating access to shared data. It does not automatically fix every slow query or remove the need for careful design.

Start With the Bottleneck

A disk-based table can already have its active pages cached in memory. Moving to memory-optimized tables changes more than storage location. The engine uses different data structures and concurrency mechanisms for those tables.

That makes contention a useful starting question. Frequent short transactions competing for shared structures can be promising candidates. A report scanning excessive rows for an unnecessary join needs a different investigation.

SELECT TOP (15) wait_type, waiting_tasks_count,
       wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

These are accumulated instance waits, not a diagnosis of one table. Compare samples over a representative interval and identify the affected requests. Do not attribute every latch wait to an application table.

Check the Platform and Existing Objects

Confirm the installed version, edition, and feature support before designing a migration. Feature availability and capacity limits depend on the platform. A working Developer Edition experiment does not establish production licensing or capacity.

SELECT SERVERPROPERTY('ProductVersion') AS product_version,
       SERVERPROPERTY('Edition') AS edition,
       SERVERPROPERTY('IsXTPSupported') AS xtp_supported;
SELECT SCHEMA_NAME(schema_id) AS schema_name, name,
       is_memory_optimized, durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;

These queries inspect support and existing tables without creating anything. Metadata visibility depends on permissions. An empty table list means no visible matching objects, not proof that the entire server lacks the feature.

Choose Durability Deliberately

A durable memory-optimized table uses SCHEMA_AND_DATA durability. Its data remains recoverable through the supported logging and checkpoint mechanisms. Memory residency does not mean that persistence disappears.

SCHEMA_ONLY preserves the table definition but does not preserve its contents across a server restart. That can fit replaceable transient data. It is the wrong choice for orders that must survive a failure.

Write the durability requirement before testing performance. Include restart and recovery behavior in the experiment. An impressive throughput figure is irrelevant if the chosen table loses data the application must retain.

Budget for More Than the Current Rows

Memory planning includes data, indexes, row versions, and workload growth. Long-running transactions can delay removal of old versions. Concurrent activity can therefore raise memory demand beyond a quiet table-size estimate.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name,
       OBJECT_NAME(object_id) AS table_name,
       memory_allocated_for_table_kb,
       memory_used_by_table_kb,
       memory_allocated_for_indexes_kb,
       memory_used_by_indexes_kb
FROM sys.dm_db_xtp_table_memory_stats
WHERE object_id > 0;

Run the query inside the relevant database with the required monitoring permission. Record samples during peak activity, not just after loading data. Leave headroom for the rest of SQL Server and the operating system.

Do not assume memory-optimized tables can spill their active contents to disk when memory becomes scarce. Test the application's response to resource pressure. A capacity plan needs an operational response as well as a number.

Review Transactions and Schema Changes

Optimistic concurrency can reject conflicting transactions instead of making them wait in the familiar way. The application needs appropriate retry handling. Retry the complete logical transaction and prevent duplicate external effects.

Cross-database transactions involving memory-optimized tables have important restrictions. Review the documented exceptions and the actual transaction boundaries. Replacing one table can affect procedures that previously crossed databases without difficulty.

The old claim that memory-optimized tables cannot be altered is outdated. Modern SQL Server supports documented ALTER TABLE operations, including supported index changes. Their restrictions and availability impact still require planning.

SELECT SCHEMA_NAME(t.schema_id) AS schema_name,
       t.name AS table_name, i.name AS index_name,
       i.type_desc
FROM sys.tables AS t
JOIN sys.indexes AS i ON i.object_id = t.object_id
WHERE t.is_memory_optimized = 1
  AND i.index_id > 0;

Index choice still matters. Hash indexes favor equality lookups and require sensible bucket sizing. A workload dominated by ranges needs suitable ordered indexes rather than an assumption that every memory lookup is equivalent.

Test a Small Representative Path

Choose one transaction path with measured contention and a clear success criterion. Preserve the original workload shape, concurrency, and durability requirements. Compare throughput, latency distribution, conflicts, memory, and recovery behavior.

Test interpreted access and native compilation according to their separate requirements. Converting a table does not automatically convert every procedure. Record which change produced any measured improvement.

Keep the migration decision tied to operational benefit. Include schema deployment, monitoring, troubleshooting, and the return path in the trial. Use the feature when the complete result justifies the additional design work.

In-Memory OLTP is not a universal speed switch, it is a targeted transaction-processing design.

This post was rewritten from scratch in September 2026. The original, published on 2014-04-27, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.