Leveraging In-Memory Technology for Improved Performance

revisited in 2026 after the original 2014 article

Introduction

SQL Server's memory-optimized tables, part of the In-Memory OLTP feature introduced in SQL Server 2014, are designed to significantly improve the performance of transactional workloads. Their data lives in memory and is accessed through lock-free, latch-free structures instead of disk-based pages and the buffer pool. Durable memory-optimized tables are still persisted to disk (through the transaction log and checkpoint files), so you get the speed of in-memory access without giving up recoverability. In this article, we will explore memory-optimized tables, their benefits, and how to create and manage them in SQL Server.

A note on versions and editions: In-Memory OLTP was Enterprise-only in SQL Server 2014 and the original 2016 release. Since SQL Server 2016 SP1 it is available in other editions too, subject to per-edition memory limits. Many of the restrictions described below were also relaxed in SQL Server 2016 and later, so check the documentation for the version you are running.

Benefits of Memory-Optimized Tables

Enhanced performance: Memory-optimized tables are read directly from memory, so queries don't wait on page reads from disk. Durable tables still write to the transaction log, but logging is leaner than with disk-based tables (index changes aren't logged, for example), and tables created as SCHEMA_ONLY skip data logging entirely. The result is faster queries and higher throughput, which makes these tables a good fit for workloads with high transaction rates or low-latency requirements.

Reduced contention: Traditional disk-based tables can suffer from lock and latch contention. Memory-optimized tables use a lock-free and latch-free design based on optimistic, multiversion concurrency control, so readers and writers don't block one another. The trade-off is that conflicts surface as errors instead of waits. For example, two transactions updating the same row cause the later one to fail with a write-write conflict (error 41302), so applications should include retry logic.

Optimized data structures: Memory-optimized tables don't use 8 KB pages. Rows are linked through in-memory index structures, and updates create new row versions, with old versions cleaned up later by a background garbage collection process. This design is what removes the need for locks and latches and improves concurrency and throughput.

Reduced index maintenance: Because there are no pages, memory-optimized indexes don't fragment, and there is no rebuild or reorganize routine to schedule. They are not maintenance-free, however: hash indexes need a sensibly sized bucket count, and statistics must be kept current (SQL Server 2014 requires updating them manually; SQL Server 2016 and later can update them automatically at compatibility level 130 or higher).

There are a few important points to consider when managing memory-optimized tables:

1. Durability options: Memory-optimized tables can be created with two durability options: SCHEMA_AND_DATA (the default) and SCHEMA_ONLY. SCHEMA_AND_DATA persists both the table definition and its data, so both survive a restart. SCHEMA_ONLY persists only the definition. It is faster because nothing is logged or written to checkpoint files, but all data is lost when the server restarts or crashes. Note that durable tables must have a primary key, while SCHEMA_ONLY tables do not.
2. Indexing: Memory-optimized tables support hash and nonclustered (range) indexes. Hash indexes are optimized for equality (point) lookups, and their bucket count should be sized to roughly the number of distinct key values you expect. Nonclustered indexes are optimized for range scans and ordered retrieval. In SQL Server 2014, indexes must be declared when the table is created and each table is limited to eight; SQL Server 2016 and later lift both restrictions.
3. Limitations: Memory-optimized tables have restrictions compared to traditional disk-based tables, and they depend on the version. In SQL Server 2014, they do not support foreign keys, CHECK or UNIQUE constraints (other than the primary key), identity columns, triggers, or the ALTER TABLE statement, so any schema change means recreating the table. SQL Server 2016 and later support ALTER TABLE (as an offline operation that rebuilds the table, which temporarily needs additional memory), foreign keys between memory-optimized tables, CHECK and UNIQUE constraints, IDENTITY(1,1), and AFTER triggers. Foreign keys to disk-based tables and identity columns with other seeds or increments are still unsupported.
4. Monitoring and capacity planning: Memory-optimized data must fit in memory, and it cannot be paged out to disk. Plan for the size of your data plus its indexes and the extra row versions that exist until garbage collection catches up. Monitor memory usage using tools such as the Vroom Performance Suite or the sys.dm_db_xtp_table_memory_stats DMV, and consider binding the database to a Resource Governor resource pool so memory-optimized data can't starve the rest of the instance. If memory runs out, transactions that need more memory-optimized storage will fail, and the rest of the instance, including traditional tables, will feel the pressure.

Creating Memory-Optimized Tables

To create a memory-optimized table in SQL Server, you must first create a memory-optimized filegroup and add a container to it to store the memory-optimized data. Then, you can create the table using the CREATE TABLE statement with the MEMORY_OPTIMIZED = ON option.

Create a memory-optimized filegroup:

ALTER DATABASE YourDatabaseName ADD FILEGROUP YourFileGroupName CONTAINS MEMORY\_OPTIMIZED\_DATA;

Add a container to the filegroup:

The container is a directory, not a single file. SQL Server creates the checkpoint files inside it.

ALTER DATABASE YourDatabaseName ADD FILE (NAME = 'YourContainerName', FILENAME = 'C:\\YourFilePath\\YourContainerName\_directory') TO FILEGROUP YourFileGroupName;

Create a memory-optimized table:

CREATE TABLE dbo.YourMemoryOptimizedTableName ( Column1 INT NOT NULL PRIMARY KEY NONCLUSTERED, Column2 NVARCHAR(50) NOT NULL, Column3 DATETIME NOT NULL ) WITH (MEMORY\_OPTIMIZED = ON, DURABILITY = SCHEMA\_AND\_DATA);

The primary key must be declared as either NONCLUSTERED or a hash index, since clustered indexes are not available on memory-optimized tables.

The DURABILITY option specifies the durability level for the memory-optimized table. There are two options:

SCHEMA_AND_DATA (default): Provides full durability for both schema and data. The table definition and its data are persisted to disk and survive server restarts.

SCHEMA_ONLY: When SQL Server is restarted, the table definition survives but all data in the table is lost.

Use Cases for Memory-Optimized Tables

Memory-optimized tables are particularly well-suited for the following scenarios:

High-concurrency workloads: Applications with a high volume of concurrent transactions, such as e-commerce platforms, can benefit from the reduced contention and improved throughput. They are especially helpful for hot-spot inserts, where many sessions write to the end of the same index and hit latch contention on disk-based tables.

Real-time analytics: Memory-optimized tables are fast at ingesting and looking up transactional data, which supports real-time reporting against live data. For aggregation-heavy queries, SQL Server 2016 and later allow a columnstore index on a memory-optimized table, enabling real-time operational analytics.

Caching: Storing frequently accessed data in SCHEMA_ONLY memory-optimized tables can serve as an efficient caching mechanism that reduces I/O overhead and improves application performance, as long as the cached data can be rebuilt after a restart.

Session state management: Memory-optimized tables can be used to manage session state in web applications, allowing for faster session data retrieval and updates. SCHEMA_ONLY is common here when losing sessions on a restart is acceptable.

Transient and staging data: Applications that process short-lived data, such as data streams, message queues, or ETL staging, can leverage memory-optimized tables for faster processing and reduced latency. (This is different from SQL Server's temporal tables, which are an unrelated feature.)

Conclusion

Memory-optimized tables in SQL Server offer a powerful solution for enhancing performance and scalability in high-concurrency and low-latency scenarios. They are not a drop-in replacement for every table, though. Memory must be sized for them, some features are restricted depending on your version, and applications need to handle optimistic concurrency conflicts. By understanding the benefits, use cases, and management considerations, organizations can effectively leverage memory-optimized tables to improve application performance and meet growing data processing demands. To get the most out of them, consider pairing them with natively compiled stored procedures.

Closing Thoughts

The next person that makes the blanket statement that "RAM is cheap" has to send me some. That is all...