SQL Server In-Memory OLTP: Memory-Optimized Tables, MVCC and Native Compilation

SQL Server In-Memory OLTP: Memory-Optimized Tables, MVCC and Native Compilation

SQL Server already uses memory extensively. It caches frequently accessed pages in the buffer pool, keeps execution plans in memory and gives memory to operations such as sorting, joining and aggregation.

So what does In-Memory OLTP provide that an ordinary, well-cached SQL Server database does not?

The answer is not simply faster storage.

In-Memory OLTP is a separate transaction-processing architecture inside SQL Server, built around memory-resident rows, lock-free indexes, multiversion concurrency control and optional native compilation.

A memory-optimized table is not an ordinary table that SQL Server tries harder to cache. It is created and managed using a different storage engine.

Disk-based table
Rows stored in database pages
Pages loaded through the buffer pool
Traditional clustered and nonclustered indexes
Locks and latches coordinate access

Memory-optimized table
Rows held in memory-optimized structures
No traditional data pages or buffer-pool access
Hash and memory-optimized nonclustered indexes
MVCC and optimistic transaction validation

In-Memory OLTP can produce significant improvements when an application is constrained by lock contention, latch contention, page-oriented access, repeated transaction-processing overhead, tempdb contention or extremely frequent stored-procedure execution.

It will not automatically repair poor queries, slow application code or network latency.

Which measured OLTP bottleneck would a memory-optimized design remove?

---

1. Begin with OLTP

OLTP stands for Online Transaction Processing.

An OLTP application handles large numbers of relatively small business transactions:

• creating an order;
• reserving stock;
• recording a payment;
• updating a customer balance;
• changing a workflow status;
• adding an audit event;
• maintaining session state;
• receiving telemetry.

A typical transaction affects a small number of rows and is expected to finish quickly while many other users are active.

OLTP workload
Many short transactions
Small row sets
Frequent inserts and updates
High concurrency
Low latency required

Analytical workload
Fewer, larger queries
Large scans and aggregations
Historical reporting
Throughput over individual latency

In-Memory OLTP is designed primarily for the first workload. It should not be confused with columnstore indexes, which are designed largely for analytical processing.

---

2. Why keeping an ordinary table in cache is not the same

Suppose an ordinary Orders table is accessed constantly. SQL Server may keep nearly all its data pages in memory. Physical disk reads may become rare.

That helps, but the table still uses the traditional database architecture:

Query
  ↓
Buffer pool
  ↓
8 KB data and index pages
  ↓
Locks, latches and page-based structures

In-Memory OLTP changes those structures:

Query
  ↓
In-Memory OLTP engine
  ↓
Memory-resident rows
  ↓
Memory pointers and lock-free indexes

The opportunity is larger than eliminating physical reads. In-Memory OLTP attempts to reduce page navigation, buffer-pool interaction, traditional lock management, latch coordination, index-page maintenance, interpreted procedural execution and some transaction-log overhead.

This is why adding more RAM to an existing server does not provide the same architecture.

---

3. The three foundations of In-Memory OLTP

Memory-optimized tables and indexes

Rows and indexes use structures designed for direct memory access. They are not organised into the conventional pages and extents used by disk-based tables.

Multiversion concurrency control

SQL Server maintains row versions and validates transactions optimistically. Readers can access the version appropriate to their transaction without waiting for a writer to release a traditional row lock.

Native compilation

Selected stored procedures can be compiled into native machine code. This reduces instruction-processing overhead for small, frequently executed, performance-critical operations.

These features can be adopted progressively. An application can first access a memory-optimized table through ordinary T-SQL. Native compilation can be considered later for proven hot paths.

---

Part I - Memory-Optimized Tables

4. You create a different kind of table

You do not normally convert an existing table using syntax such as:

ALTER TABLE dbo.Orders
SET MEMORY_OPTIMIZED = ON;

Instead, the migration model is:

Existing disk-based table
        ↓
Prepare the database
        ↓
Create memory-optimized table
        ↓
Copy and validate data
        ↓
Redirect application traffic
        ↓
Measure the result

Begin with an ordinary table:

CREATE TABLE dbo.Orders
(
    OrderId     bigint         NOT NULL,
    CustomerId int            NOT NULL,
    OrderDate   datetime2      NOT NULL,
    Amount      decimal(18, 2) NOT NULL,
    Status      varchar(20)    NOT NULL,

    CONSTRAINT PK_Orders
        PRIMARY KEY CLUSTERED (OrderId)
);

This is a traditional disk-based table.

---

5. Prepare the SQL Server database

On SQL Server, the database requires one filegroup marked for memory-optimized data:

ALTER DATABASE ShopDb
ADD FILEGROUP ShopDb_Memory
CONTAINS MEMORY_OPTIMIZED_DATA;

Add a container:

ALTER DATABASE ShopDb
ADD FILE
(
    NAME = 'ShopDb_Memory_Container',
    FILENAME = 'C:\SQLData\ShopDb_Memory_Container'
)
TO FILEGROUP ShopDb_Memory;

The parent path must be suitable for the SQL Server installation and service account.

Why does an in-memory feature need disk storage?

Durable memory-optimized data must survive a restart. SQL Server writes recovery information into data and delta checkpoint files and uses the transaction log. When the database comes online, SQL Server reconstructs the in-memory representation from its durable information.

Even SCHEMA_ONLY memory-optimized tables require the special filegroup on SQL Server, although their rows are not recovered. Azure SQL Database manages this infrastructure and does not require the application team to create this filegroup.

---

6. Create a durable memory-optimized Orders table

USE ShopDb;
GO

CREATE TABLE dbo.Orders_InMemory
(
    OrderId     bigint         NOT NULL,
    CustomerId int            NOT NULL,
    OrderDate   datetime2      NOT NULL,
    Amount      decimal(18, 2) NOT NULL,
    Status      varchar(20)    NOT NULL,

    CONSTRAINT PK_Orders_InMemory
        PRIMARY KEY NONCLUSTERED (OrderId),

    INDEX IX_Orders_InMemory_CustomerId
        NONCLUSTERED (CustomerId),

    INDEX IX_Orders_InMemory_OrderDate
        NONCLUSTERED (OrderDate)
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_AND_DATA
);

The defining option is:

MEMORY_OPTIMIZED = ON

This tells SQL Server to create the table through the In-Memory OLTP engine.

The primary key is explicitly NONCLUSTERED because memory-optimized tables do not use the traditional clustered-index structure. Every memory-optimized table must have at least one index. A durable SCHEMA_AND_DATA table must have a primary key.

---

7. Durability is a business decision

SCHEMA_AND_DATA

DURABILITY = SCHEMA_AND_DATA

The table definition and committed rows survive SQL Server restarts, backup and restore, and supported failover and recovery processes.

This is appropriate for orders, payments, stock reservations, account balances and customer actions.

SCHEMA_ONLY

DURABILITY = SCHEMA_ONLY

Only the table definition survives. The rows disappear when the database is restarted or recovered elsewhere.

This can be appropriate for session state, transient staging data, temporary calculation results, replaceable caches and high-throughput work buffers.

SCHEMA_AND_DATA
Definition survives
Committed rows survive
Logging and recovery required

SCHEMA_ONLY
Definition survives
Rows do not survive
Reduced durability work

An Orders table should almost certainly use SCHEMA_AND_DATA. A performance improvement does not justify losing customer orders.

---

8. How durable rows survive a restart

During ordinary processing, durable rows are accessed through their in-memory representation.

For recovery, SQL Server maintains transaction-log records, data files representing inserted rows, delta files identifying deleted rows and checkpoint information.

On restart, SQL Server loads the durable data into memory and applies transaction-log records not already represented by the checkpoint files. The in-memory indexes are reconstructed.

A very large memory-optimized database may therefore require significant memory and recovery time. Checkpoint-file storage also requires monitoring. Regular checkpoints and transaction-log backups help old files move through their lifecycle.

---

9. Copy and verify the data

INSERT INTO dbo.Orders_InMemory
(
    OrderId,
    CustomerId,
    OrderDate,
    Amount,
    Status
)
SELECT
    OrderId,
    CustomerId,
    OrderDate,
    Amount,
    Status
FROM dbo.Orders;

Verify the table:

SELECT
    name,
    is_memory_optimized,
    durability_desc
FROM sys.tables
WHERE name = 'Orders_InMemory';

Then reconcile the data:

SELECT COUNT(*) FROM dbo.Orders;
SELECT COUNT(*) FROM dbo.Orders_InMemory;

SELECT SUM(Amount) FROM dbo.Orders;
SELECT SUM(Amount) FROM dbo.Orders_InMemory;

A production migration should also compare business totals, constraints, representative records, concurrent writes, restart behaviour and recovery.

---

Part II - MVCC and Concurrency

10. Memory-optimized rows can have multiple versions

A memory-optimized row contains a header and payload.

Row header
- begin timestamp
- end timestamp
- statement information
- index pointers

Row payload
- OrderId
- CustomerId
- OrderDate
- Amount
- Status

The timestamps determine which transactions can see each version.

Version 1
OrderId: 501
Status: Pending
Visible from time 100 to 200

Version 2
OrderId: 501
Status: Dispatched
Visible from time 200 onwards

A transaction using an earlier logical view can still see Pending. A later transaction sees Dispatched.

An update conceptually expires the existing row version and creates a new one. Old versions are removed after no active transaction can see them. This cleanup is commonly described as garbage collection.

---

11. Lock-free does not mean conflict-free

In-Memory OLTP uses optimistic concurrency. Transactions proceed on the assumption that conflicts will be uncommon, and SQL Server validates the transaction before committing.

Request A reads AvailableQuantity = 1
Request B reads AvailableQuantity = 1

Request A reserves the item
Request B tries to reserve the same item

SQL Server detects the conflict
One transaction must fail and retry or report failure

Traditional blocking is reduced, but application design still matters. The application needs short transaction boundaries, conflict-aware retry handling, idempotent commands, duplicate protection, sensible retry limits and useful telemetry.

A retry must not repeat an irreversible business action. Blindly retrying a combined “charge card and save order” operation could charge the customer twice unless the payment workflow uses an idempotency key.

---

12. ASP.NET Core conflict-retry example

public sealed class InMemoryOrderWriter
{
    private readonly string _connectionString;
    private readonly ILogger<InMemoryOrderWriter> _logger;

    public InMemoryOrderWriter(
        IConfiguration configuration,
        ILogger<InMemoryOrderWriter> logger)
    {
        _connectionString =
            configuration.GetConnectionString("ShopDb")
            ?? throw new InvalidOperationException(
                "ShopDb connection string is missing.");

        _logger = logger;
    }

    public async Task MarkDispatchedAsync(
        long orderId,
        CancellationToken cancellationToken)
    {
        const int maximumAttempts = 3;

        for (var attempt = 1; attempt <= maximumAttempts; attempt++)
        {
            try
            {
                await UpdateOrderAsync(orderId, cancellationToken);
                return;
            }
            catch (SqlException exception)
                when (IsRetryableConflict(exception)
                      && attempt < maximumAttempts)
            {
                var delay = TimeSpan.FromMilliseconds(
                    25 * Math.Pow(2, attempt - 1));

                _logger.LogWarning(
                    exception,
                    "Transaction conflict updating order {OrderId}. " +
                    "Retrying attempt {Attempt}.",
                    orderId,
                    attempt + 1);

                await Task.Delay(delay, cancellationToken);
            }
        }
    }

    private async Task UpdateOrderAsync(
        long orderId,
        CancellationToken cancellationToken)
    {
        await using var connection =
            new SqlConnection(_connectionString);

        await connection.OpenAsync(cancellationToken);

        await using var transaction =
            (SqlTransaction)await connection.BeginTransactionAsync(
                cancellationToken);

        await using var command = new SqlCommand(
            """
            UPDATE dbo.Orders_InMemory
            SET Status = 'Dispatched'
            WHERE OrderId = @OrderId
              AND Status = 'Paid';
            """,
            connection,
            transaction);

        command.Parameters.Add(
            new SqlParameter("@OrderId", SqlDbType.BigInt)
            {
                Value = orderId
            });

        var affectedRows =
            await command.ExecuteNonQueryAsync(cancellationToken);

        if (affectedRows != 1)
        {
            throw new InvalidOperationException(
                "The order was not in the expected Paid state.");
        }

        await transaction.CommitAsync(cancellationToken);
    }

    private static bool IsRetryableConflict(SqlException exception)
    {
        // Verify and maintain this list against the SQL Server
        // version and errors observed by the application.
        return exception.Number is 41302 or 41305 or 41325;
    }
}

The principles matter more than the exact helper:

• retry only known transient errors;
• cap attempts;
• use a small backoff;
• honour cancellation;
• log conflicts;
• keep the transaction idempotent;
• never retry business-rule failures as infrastructure faults.

---

Part III - Memory-Optimized Indexes

13. Why every table needs an index

A disk-based table can exist as a heap. A memory-optimized table cannot.

Memory-optimized rows are connected and reached through indexes. Every table therefore requires at least one index.

The principal transactional choices are hash indexes and memory-optimized nonclustered indexes. Both are memory-resident and lock-free, but they solve different access patterns.

---

14. Hash indexes are for complete-key equality lookups

A hash index applies a hash function to the index key and maps it to a bucket.

OrderId = 8209
        ↓
Hash function
        ↓
Bucket 147
        ↓
Matching row pointer

CONSTRAINT PK_Orders_InMemory
    PRIMARY KEY NONCLUSTERED HASH (OrderId)
    WITH (BUCKET_COUNT = 1000000)

This can be extremely effective for:

SELECT *
FROM dbo.Orders_InMemory
WHERE OrderId = 8209;

A hash index is not designed for ranges or ordering:

WHERE OrderId > 8209

WHERE OrderId BETWEEN 8000 AND 9000

ORDER BY OrderId

It has no useful key order.

---

15. Composite hash keys require the complete key

INDEX IX_Order_Customer
    HASH (CustomerId, OrderId)
    WITH (BUCKET_COUNT = 1000000)

This can serve:

WHERE CustomerId = 42
  AND OrderId = 8209

But this does not provide the complete hash key:

WHERE CustomerId = 42

SQL Server cannot calculate the same composite hash without OrderId. This differs from an ordered composite index, where a leading key may still support navigation.

---

16. Bucket count controls the hash structure

Too few buckets produce long collision chains. Too many reserve unnecessary memory and can make scans more expensive because many buckets are empty.

Current guidance generally places the ideal bucket count around one to two times the expected number of distinct key values. Moderate overestimation is normally safer than severe underestimation, but duplication and growth must be considered.

SELECT
    object_name(hs.object_id) AS TableName,
    i.name AS IndexName,
    hs.total_bucket_count,
    hs.empty_bucket_count,
    hs.avg_chain_len,
    hs.max_chain_len
FROM sys.dm_db_xtp_hash_index_stats AS hs
JOIN sys.indexes AS i
    ON i.object_id = hs.object_id
   AND i.index_id = hs.index_id;

Watch for high chain lengths, extreme duplication, excessive empty buckets and growth beyond the original estimate.

---

17. Memory-optimized nonclustered indexes support ranges

INDEX IX_Orders_InMemory_OrderDate
    NONCLUSTERED (OrderDate)

This index can support equality, ranges and useful ordering:

WHERE OrderDate = '2026-08-19'

WHERE OrderDate >= '2026-08-01'
  AND OrderDate <  '2026-09-01'

ORDER BY OrderDate

When the access pattern is uncertain, begin with a memory-optimized nonclustered index and consider a hash index only after measuring a stable equality-heavy workload.

---

18. Memory-optimized indexes are naturally covering

A disk-based nonclustered index may need INCLUDE columns to avoid a key lookup.

Memory-optimized indexes point directly to memory-resident rows containing the complete payload. Included columns are neither required nor supported in the same way.

This does not make indexes free. Each adds memory use, an index pointer in every row, write work and recovery reconstruction work.

---

Part IV - Native Compilation

19. Ordinary T-SQL can access memory-optimized tables

You do not have to rewrite every stored procedure immediately.

SELECT
    OrderId,
    CustomerId,
    OrderDate,
    Amount,
    Status
FROM dbo.Orders_InMemory
WHERE CustomerId = @CustomerId;

This can be submitted through ADO.NET, Dapper, compatible EF Core access or an interpreted stored procedure.

Disk-based table + interpreted T-SQL
        ↓
Memory-optimized table + interpreted T-SQL
        ↓
Measure table-engine benefit
        ↓
Native procedure for selected hot path
        ↓
Measure native-compilation benefit

---

20. Create a natively compiled stored procedure

CREATE PROCEDURE dbo.GetCustomerOrders
    @CustomerId int
WITH
    NATIVE_COMPILATION,
    SCHEMABINDING,
    EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH
(
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'us_english'
)
    SELECT
        OrderId,
        CustomerId,
        OrderDate,
        Amount,
        Status
    FROM dbo.Orders_InMemory
    WHERE CustomerId = @CustomerId;
END;

NATIVE_COMPILATION requests native code. SCHEMABINDING binds the procedure to its referenced objects. EXECUTE AS fixes the security context. BEGIN ATOMIC defines one atomic transaction block with explicit isolation and language behaviour.

---

21. What native compilation does

T-SQL
  ↓
Parsing and binding
  ↓
Query optimization
  ↓
Internal execution representation
  ↓
Native-code generation
  ↓
Compiled DLL
  ↓
Loaded into SQL Server

SQL Server manages the generated files. They should not be edited or deployed manually.

---

22. Native compilation remains specialised in SQL Server 2025

SQL Server 2025 has a more mature implementation than SQL Server 2014, but native procedures still do not support every T-SQL feature or query shape.

Important considerations include:

• native procedures are single-threaded;
• they do not use parallel plans;
• Hash Join and Merge Join are not available in native plans;
• some T-SQL constructs remain unsupported;
• they access memory-optimized rather than arbitrary disk-based tables;
• statistics changes do not automatically recompile native procedures like interpreted plans.

They suit short transactions, predictable access paths, frequent operations, small row sets and high-concurrency hot paths. They are generally unsuitable for analytical reports, large scans, ad hoc searching and queries that benefit from parallelism.

---

Part V - Modern In-Memory Patterns

23. Memory-optimized table variables

A useful modern pattern does not require migrating a permanent business table.

CREATE TYPE dbo.OrderIdList AS TABLE
(
    OrderId bigint NOT NULL,

    INDEX IX_OrderId
        NONCLUSTERED HASH (OrderId)
        WITH (BUCKET_COUNT = 1024)
)
WITH
(
    MEMORY_OPTIMIZED = ON
);

Use it as a table variable:

DECLARE @OrderIds dbo.OrderIdList;

INSERT INTO @OrderIds (OrderId)
VALUES (101), (102), (103);

SELECT o.*
FROM dbo.Orders_InMemory AS o
JOIN @OrderIds AS ids
    ON ids.OrderId = o.OrderId;

Memory-optimized table variables avoid tempdb, require at least one index and can be passed as table-valued parameters.

---

24. ASP.NET Core table-valued parameter example

var orderIds = new DataTable();
orderIds.Columns.Add("OrderId", typeof(long));

orderIds.Rows.Add(101L);
orderIds.Rows.Add(102L);
orderIds.Rows.Add(103L);

await using var connection =
    new SqlConnection(connectionString);

await connection.OpenAsync(cancellationToken);

await using var command = new SqlCommand(
    "dbo.GetOrdersByIds",
    connection)
{
    CommandType = CommandType.StoredProcedure
};

var parameter = command.Parameters.AddWithValue(
    "@OrderIds",
    orderIds);

parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.OrderIdList";

await using var reader =
    await command.ExecuteReaderAsync(cancellationToken);

This is preferable to sending hundreds of commands, building a large unparameterised IN string or repeatedly creating temporary tables.

---

25. SCHEMA_ONLY tables as shared temporary structures

CREATE TABLE dbo.ActiveImportRows
(
    SessionId uniqueidentifier NOT NULL,
    RowNumber int              NOT NULL,
    Payload   nvarchar(1000)   NOT NULL,

    INDEX IX_ActiveImportRows
        NONCLUSTERED (SessionId, RowNumber)
)
WITH
(
    MEMORY_OPTIMIZED = ON,
    DURABILITY = SCHEMA_ONLY
);

The application must separate sessions explicitly:

WHERE SessionId = @SessionId

Unlike a connection-scoped temporary table, rows do not disappear merely because one connection closes. The application may need explicit cleanup, expiration timestamps and protection against abandoned data.

---

26. Modern support versus original Chapter 7 limitations

The original SQL Server 2014 release had significant restrictions. Later versions added or improved:

• ALTER TABLE support;
• ALTER PROCEDURE support;
• automatic statistics updates;
• foreign-key, CHECK and UNIQUE constraints;
• computed columns;
• broader collation support;
• removal of the original eight-index limit;
• multithreaded checkpoint processing;
• multithreaded recovery;
• broader T-SQL support;
• improved tooling and monitoring.

SQL Server 2025 continues the same central architecture rather than introducing a separate new in-memory engine.

Important current limitations remain:

• no cross-database transactions involving memory-optimized tables;
• no distributed transactions for those operations;
• database snapshots are unavailable for databases with a memory-optimized filegroup;
• filtered indexes are unsupported;
• included index columns are unnecessary and unsupported;
• DBCC CHECKDB does not validate memory-optimized rows like disk-based rows;
• native modules support a restricted T-SQL surface.

Always verify compatibility against the documentation for the exact SQL Server release and deployment platform.

---

Part VI - Operations and Monitoring

27. Capacity planning is essential

Memory-optimized tables must fit within the memory available to the In-Memory OLTP engine.

Estimate business-column data, row headers, index pointers, hash buckets, nonclustered indexes, concurrent row versions, future growth, temporary spikes and safety headroom.

Do not size the server from the current disk size alone. Disk compression, page layout and memory-optimized row layout differ.

---

28. Protect memory with Resource Governor

A SQL Server database containing memory-optimized tables can be bound to a Resource Governor pool.

SQL Server memory
    ├── Default pool
    │   ├── Buffer pool
    │   ├── Query grants
    │   └── Other databases
    │
    └── In-Memory OLTP pool
        └── Selected database

This can reserve predictable capacity, isolate consumption and make pool allocation easier to monitor. The pool must be based on realistic data, index, row-version and growth estimates.

---

29. Monitor table and index memory

SELECT
    object_name(object_id) AS ObjectName,
    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
ORDER BY
    memory_used_by_table_kb
    + memory_used_by_indexes_kb DESC;

Monitor transaction throughput, latency percentiles, validation failures, retry count, memory consumption, row-version growth, hash-chain lengths, checkpoint-file growth, recovery time, log throughput and application error rate.

A system that is faster but regularly fails transactions or exhausts memory is not an improvement.

---

Part VII - A Safe Adoption Plan

30. Do not begin by migrating the largest table

Step 1: Find the bottleneck

Capture wait statistics, blocking, deadlocks, latch waits, log latency, execution frequency, CPU, tempdb contention and application latency.

Step 2: Choose a narrow candidate

Good candidates include a hot status table, session-state table, frequently updated counter, event-ingestion table or heavily used temporary structure.

Step 3: Confirm compatibility

Review constraints, triggers, data types, foreign keys, cross-database access, replication, availability, backup and restore, ORM behaviour and stored procedures.

Step 4: Estimate memory

Include data, growth, indexes, row versions and operating headroom.

Step 5: Build a realistic prototype

Use representative volume, key distribution and concurrency.

Step 6: Test interpreted T-SQL first

This isolates the benefit of the table engine.

Step 7: Test native compilation separately

Apply it only to a measured hot procedure.

Step 8: Test failures

Include transaction conflicts, cancellation, restart, recovery, memory pressure, log pressure and partial deployment.

Step 9: Prepare migration and rollback

Define exactly how traffic, data and schema will move.

Step 10: Compare with the baseline

Keep the change only if it produces a meaningful, supportable improvement.

---

31. The final mental model

Memory-optimized table
A different row-storage and transaction engine

SCHEMA_AND_DATA
Rows survive recovery

SCHEMA_ONLY
Rows are intentionally transient

MVCC
Transactions see appropriate row versions

Optimistic validation
Conflicts fail instead of waiting indefinitely

Hash index
Complete-key equality lookup

Memory-optimized nonclustered index
Equality, ranges and ordered access

Native compilation
Performance-critical T-SQL compiled to machine code

Memory-optimized table variable
Temporary relational data without tempdb

Resource Governor
Capacity protection for In-Memory OLTP memory

In-Memory OLTP does not make a normal table remain cached. It replaces selected page-oriented, lock-coordinated processing with memory-resident rows, optimistic versioning and specialised indexes.

The right question is not:

Can we put this database in memory?

It is:

Where is our transactional bottleneck, which In-Memory OLTP feature addresses it, and how will we prove the improvement safely?

That question captures the essence of In-Memory OLTP while remaining relevant to SQL Server 2025.

---

Continue the SQL Server performance journey

• How SQL Server Optimizes a Query
• How SQL Server Estimates Rows Before Running a Query
• How SQL Server Executes a Query
• SQL Server Indexes: Make Reads Faster Without Making Writes Expensive
• SQL Mentoring Series: Performance Engineering and Modern Data Architecture

If this guide helped you understand In-Memory OLTP, I would be pleased to hear from you. For mentoring, technical discussion or collaboration, contact dotnetdeveloper20xx@hotmail.com.