Introduction After spending 15 years as a SQL Server DBA, I've witnessed numerous technological evolutions, but few have offered the performance gains of Memory-Optimized Tables. Introduced in SQL Server 2014 and significantly enhanced in subsequent versions, this technology represents a fundamental shift in how databases store and access data. Unlike traditional disk-based tables that must be read from disk into memory, Memory-Optimized Tables reside entirely in memory with disk storage serving only as a durability mechanism. This architectural difference eliminates buffer pool management overhead, lock contention, and latch contention that typically plague high-throughput OLTP systems. Today, I'll share my practical experience on when these tables make sense, why you should consider them, and how to implement them correctly in your environment. When to Use Memory-Optimized Tables Memory-Optimized Tables shine in specific scenarios that traditional tables struggle with. Consider implementing them when dealing with high-throughput OLTP workloads where thousands of transactions occur per second. Session state management systems, trading platforms, gaming leaderboards, and IoT data ingestion layers are prime candidates. I once worked with a financial services client whose order processing system experienced timeout issues during peak hours. Their critical order table saw 5,000 transactions per second with heavy contention. After migrating to a Memory-Optimized Table, latency dropped by 95% and throughput increased by 30x. These tables are also excellent for eliminating tempdb contention. If your system heavily utilizes temporary tables (#temp tables) for complex operations, consider using memory-optimized table variables instead. A client's ETL process that generated thousands of temp tables daily was causing severe tempdb bottlenecks. Switching to memory-optimized table variables eliminated this contention entirely and reduced ETL runtime by 40%. Lastly, they excel at handling extreme concurrency scenarios. A ticketing system client struggled with "last seat" booking problems where hundreds of users competed for limited inventory. Traditional locking mechanisms created bottlenecks, but memory-optimized tables with optimistic concurrency solved this elegantly. Why Implement Memory-Optimized Tables The performance benefits of Memory-Optimized Tables stem from their fundamentally different architecture. Traditional tables use a row-based storage format with locks and latches to manage concurrency, while Memory-Optimized Tables use lock-free data structures and optimistic concurrency control. In my experience, this translates to 5-30x performance improvements for OLTP workloads. Memory-Optimized Tables eliminate several database engine bottlenecks simultaneously. They remove buffer pool contention since data already resides in memory. They eliminate latch contention on internal data structures. Most importantly, they use optimistic multiversion concurrency control, meaning readers don't block writers and writers don't block readers. This dramatically improves throughput in high-concurrency scenarios. Another compelling reason is their ability to use natively compiled stored procedures. When you pair Memory-Optimized Tables with natively compiled modules, SQL Server compiles your T-SQL code directly to machine code, bypassing the traditional interpretation layer. I've seen query execution times drop from milliseconds to microseconds—a 100x improvement in some cases. One retail client's checkout process went from 200ms to 3ms after this optimization, fundamentally changing their capacity planning. How to Implement Memory-Optimized Tables Implementation begins with enabling In-Memory OLTP in your database. You'll need to create a MEMORY_OPTIMIZED_DATA filegroup first. Here's a practical example: ALTER DATABASE YourDatabase
ADD FILEGROUP YourDatabase_MOD CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDatabase 
ADD FILE (NAME='YourDatabase_MOD', FILENAME='C:\Data\YourDatabase_MOD')
TO FILEGROUP YourDatabase_MOD; ALTER DATABASE YourDatabase
ADD FILEGROUP YourDatabase_MOD CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDatabase 
ADD FILE (NAME='YourDatabase_MOD', FILENAME='C:\Data\YourDatabase_MOD')
TO FILEGROUP YourDatabase_MOD; Now you can create your first Memory-Optimized Table. The syntax differs slightly from traditional tables, requiring you to specify: CREATE TABLE dbo.OrderDetails
(
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    INDEX ix_OrderID HASH (OrderID) WITH (BUCKET_COUNT = 1000000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); CREATE TABLE dbo.OrderDetails
(
    OrderDetailID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
    OrderID INT NOT NULL,
    ProductID INT NOT NULL,
    Quantity INT NOT NULL,
    INDEX ix_OrderID HASH (OrderID) WITH (BUCKET_COUNT = 1000000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); Notice the `MEMORY_OPTIMIZED = ON` specification and the `DURABILITY` setting. The `SCHEMA_AND_DATA` option ensures both schema and data persist through server restarts. For truly temporary data, you can use `SCHEMA_ONLY`, which provides better performance but doesn't persist data. Hash indexes require careful consideration. The `BUCKET_COUNT` should be 1-2 times the expected number of unique values to avoid excessive collisions. I once troubleshooted a performance issue where a client had set the bucket count to 10,000 for a column with 2 million unique values, causing severe hash collisions and negating performance benefits. For optimal performance, pair your Memory-Optimized Tables with natively compiled stored procedures: CREATE PROCEDURE dbo.GetOrderDetails
    @OrderID INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'English')
    
    SELECT OrderDetailID, ProductID, Quantity
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
END; CREATE PROCEDURE dbo.GetOrderDetails
    @OrderID INT
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'English')
    
    SELECT OrderDetailID, ProductID, Quantity
    FROM dbo.OrderDetails
    WHERE OrderID = @OrderID;
END; The `ATOMIC` block and transaction isolation level are requirements for natively compiled procedures. This procedure will execute dramatically faster than its interpreted counterpart. Migration and Considerations Rather than migrating everything at once, identify specific bottlenecks in your application. I recommend profiling your workload to find tables with the highest contention or transaction rates. Start with smaller, non-critical tables to gain experience before moving to core business tables. Not everything belongs in memory. Analytical queries, large reporting tables, or infrequently accessed data should remain on disk. Memory-Optimized Tables consume roughly 2-3 times their data size in memory due to versioning and index structures. A 10GB table might require 20-30GB of memory, so plan your server resources accordingly. Be aware of limitations. Memory-Optimized Tables don't support certain features like FOREIGN KEY constraints referencing disk-based tables, certain data types like TEXT or XML, computed columns referencing disk-based tables, and several others. In one implementation, we needed to redesign several triggers and computed columns to work within these constraints. Monitoring is essential. Use `sys.dm_db_xtp_table_memory_stats` to track memory usage and `sys.dm_db_xtp_hash_index_stats` to monitor hash index efficiency. Establish baselines and alerts for these metrics to avoid surprises. Conclusion Memory-Optimized Tables represent one of the most significant performance enhancements in SQL Server's recent history. When implemented properly for appropriate workloads, they can transform application performance and solve seemingly intractable concurrency issues. However, they're not a universal solution—they require careful planning, monitoring, and resource allocation. Over my 15 years managing SQL Server environments, I've learned that the best database solutions balance innovation with pragmatism. Memory-Optimized Tables exemplify this balance, offering revolutionary performance improvements while building on familiar SQL Server concepts. Start small, measure thoroughly, and you'll likely find these tables to be a valuable addition to your performance optimization toolkit.

Quant

How to Identify Tables Not Used in the Last Month or Three Months in SQL Server

Want Everyone Out of the Database? Here’s How the Pros Do It

Read My Stories

Portfolio

Too Long; Didn't Read

Make resilience your competitive advantage

Memory-Optimized Tables in SQL Server: When, Why and How to Implement Them

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

How to Create a Foreign Data Wrapper in PostgreSQL and Aurora PostgreSQL on AWS RDS

Building An In-memory Database in Go

How we built a real-time in-game currency service with 7.5M transactions per minute

Bloom Filter Basics in Go

The 30-Day .NET Challenge - Day 23: Span<T> over Arrays

How to Create a Foreign Data Wrapper in PostgreSQL and Aurora PostgreSQL on AWS RDS

Building An In-memory Database in Go

How we built a real-time in-game currency service with 7.5M transactions per minute

Bloom Filter Basics in Go

The 30-Day .NET Challenge - Day 23: Span<T> over Arrays

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps