Choosing Between In-Memory OLTP and Disk-Based Tables in SQL Server for High-Throughput OLTP

Decision Point

When designing a high‑throughput OLTP workload you must decide whether to store the primary tables in memory‑optimized (In‑Memory OLTP) format or keep them as traditional disk‑based tables. The decision hinges on data size, durability requirements, feature compatibility, and operational complexity.

Option Comparison

Aspect	Memory‑Optimized Table	Disk‑Based Table
Data residency	Entire table and indexes live in memory; durability via checkpoint and transaction log	Data stored on disk; indexes may be cached in buffer pool
Concurrency model	Lock‑free, latch‑free row versioning	Locking/latching based on isolation level
Typical throughput gain	2‑10× higher transactions per second when workload fits in memory	Baseline performance; scales with proper indexing and hardware
Feature support	Limited: no foreign keys, triggers, certain data types (text, ntext, image, varchar(max) off‑row), limited indexes	Full T‑SQL feature set
Operational complexity	Requires native compilation, capacity planning, monitoring of xtp memory	Standard administration, mature tooling

Trade‑Off Analysis

When to favor memory‑optimized tables:

• Working set (hot data) fits comfortably within available RAM after accounting for OS, buffer pool, and other workloads.
• Workload is heavily read‑write with short transactions that benefit from latch‑free access.
• Durability can be satisfied with SCHEMA_AND_DATA durability mode; you accept the checkpoint/log overhead.
• Schema can be adapted to the feature restrictions (e.g., replace foreign keys with application‑level checks).

When to stay with disk‑based tables:

• Total data size exceeds the memory you can allocate to the In‑Memory OLTP pool.
• You need features unavailable in memory‑optimized tables (foreign keys, triggers, large LOB columns).
• Ad‑hoc reporting or complex queries are a primary workload.
• Operational teams prefer the simplicity of standard backup/restore and point‑in‑time recovery.

Hybrid designs keep hot rows in a memory‑optimized table and move older rows to a disk‑based table using partitioned views or application routing, balancing performance and cost.

Implementation Example: Creating a Memory‑Optimized Order Table

Below is a step‑by‑step example that creates a memory‑optimized table for order header data, populates a sample row, and verifies the object type. Run these statements in SQL Server Management Studio (SSMS) or Azure Data Studio connected to a database where the MEMORY_OPTIMIZED_DATA filegroup exists.

Prerequisites

• Database compatibility level ≥ 130 (SQL Server 2016) and an existing MEMORY_OPTIMIZED_DATA filegroup.
• Permissions: CREATE TABLE, ALTER ANY DATASPACE, and INSERT on the target schema.

1. Create the table

-- Run as a user with sufficient permissions
CREATE TABLE Sales.OrderHeader_InMem
(
    OrderID        INT NOT NULL PRIMARY KEY NONCLUSTERED,
    CustomerID     INT NOT NULL,
    OrderDate      DATETIME2(7) NOT NULL,
    TotalAmount    DECIMAL(12,2) NOT NULL,
    Status         CHAR(1) NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

This creates a memory‑optimized table with a nonclustered hash index (default for primary key). The table resides in memory; durability ensures changes are logged and checkpointed.

2. Insert a test row

INSERT INTO Sales.OrderHeader_InMem (OrderID, CustomerID, OrderDate, TotalAmount, Status)
VALUES (1001, 42, SYSDATETIME(), 250.75, 'O');

3. Verify the table is memory‑optimized

SELECT name, is_memory_optimized
FROM   sys.tables
WHERE  name = N'OrderHeader_InMem' AND schema_id = SCHEMA_ID('Sales');
-- Expected result: is_memory_optimized = 1

Validation Steps

After creation, confirm that the table is consuming memory as expected and that basic DML works without blocking.

Check memory usage

SELECT  object_name, used_memory_kb, allocated_memory_kb
FROM    sys.dm_db_xtp_table_memory_stats
WHERE   object_id = OBJECT_ID('Sales.OrderHeader_InMem');

The used_memory_kb column shows actual memory consumed by table rows and indexes; allocated_memory_kb shows the reserved pool. Compare these values to the baseline memory available for the In‑Memory OLTP pool (visible via sys.dm_os_sys_info or SELECT * FROM sys.dm_os_memory_clerks WHERE type = 'MEMORYCLERK_XTP').

Simple throughput test (illustrative)

Using a stress tool such as ostress or SQLQueryStress, run a parameterized INSERT/UPDATE loop against the memory‑optimized table and capture transactions per second. Repeat the same test against an equivalent disk‑based table (same schema, MEMORY_OPTIMIZED = OFF) on the same hardware. Observe the relative TPS; the memory‑optimized version should show higher throughput if the data set fits in memory.

Limitations and Practical Checks

• Memory‑optimized tables cannot contain varchar(max), nvarchar(max), varbinary(max), text, , or image columns unless they are stored in‑row (size ≤ 8000 bytes). Attempting to create such a column will raise error 41317.
• Foreign key constraints, triggers, and check constraints are not supported. You must enforce those rules in the application or via stored procedures.
• Insufficient memory in the MEMORY_OPTIMIZED_DATA pool leads to allocation failures (error 41805) and automatic fallback to disk‑based tables, which can cause sudden performance drops.
• To monitor for memory pressure, query sys.dm_os_ring_buffers for RING_BUFFER_RESOURCE_MEMOIR or check the memory_brokers_clerk for MEMORYBROKER_XTP notifications.

Rollback (if needed)

If you decide the memory‑optimized table is unsuitable, you can drop it and recreate a disk‑based version. This operation changes state, so a rollback path is provided.

-- Drop the memory‑optimized table
DROP TABLE Sales.OrderHeader_InMem;
-- Recreate as disk‑based
CREATE TABLE Sales.OrderHeader_Disk
(
    OrderID        INT NOT NULL PRIMARY KEY,
    CustomerID     INT NOT NULL,
    OrderDate      DATETIME2(7) NOT NULL,
    TotalAmount    DECIMAL(12,2) NOT NULL,
    Status         CHAR(1) NOT NULL
);

Verify the new table with SELECT is_memory_optimized FROM sys.tables WHERE name = N'OrderHeader_Disk'; expecting a value of 0.