Memory-Optimized Tables as a Key-Value Cache

Repeated lookup work becomes a cache candidate when callers can reuse an already prepared value. A key-value cache in SQL Server can hold that disposable value in a memory-optimized table. Keep its lifetime and rebuild path as explicit as its lookup key.

Make the Key-Value Cache Disposable by Design

A cache stores derived or replaceable data. Its contents must be recoverable from an authoritative source when the cache is empty. Do not make a non-durable cache the only location holding business-critical information.

SCHEMA_ONLY preserves the table definition while its data does not survive engine restart or database recovery. Failover also means planning for an empty cache on the new active environment. That behavior is useful only when the application already supports misses and repopulation.

I define the miss path before measuring lookup performance. I also check how stale values are invalidated when the underlying data changes. A fast wrong answer is an unusually efficient support request.

Use this design for an intentionally shared SQL-side cache where database access remains part of the operation. Measure the entire call path, including network trips and serialization. Moving data into memory does not remove those costs.

Prepare a Dedicated Memory-Optimized Test Database

The examples target SQL Server 2025 on Windows, with an edition supporting In-Memory OLTP. Memory-optimized tables and native procedures originated in SQL Server 2014, but supported constructs evolved afterward. Do not assume every statement below fits the original implementation.

Create an isolated database with a memory-optimized filegroup and container. The parent directory must already exist and the new container path must be unused. SQL Server's service identity needs access to the chosen location.

USE master;
GO
IF ISNULL(CONVERT(int, SERVERPROPERTY('IsXTPSupported')),0) <> 1
    THROW 51000, 'This instance does not support In-Memory OLTP.', 1;
IF DB_ID(N'CacheLab') IS NOT NULL
    THROW 51001, 'Choose an unused test database name.', 1;
CREATE DATABASE CacheLab;
GO
ALTER DATABASE CacheLab ADD FILEGROUP CacheMemory CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE CacheLab ADD FILE
(NAME = N'CacheMemoryContainer', FILENAME = N'C:\SqlData\CacheLab_mem')
TO FILEGROUP CacheMemory;
GO
USE CacheLab;
GO

The memory-optimized filegroup is required even though this table's rows are non-durable. Allocate sufficient memory for entries, indexes, versions, and operational headroom. Non-durable storage is not a promise of unlimited capacity.

Match the Indexes to Lookup and Expiry

The complete cache key supports equality lookup through a hash index. An ordered nonclustered index on ExpiresAt supports expiration-range access. A hash index alone is not appropriate for that time-range cleanup.

CREATE TABLE dbo.KeyValueCache
(
    CacheKey nvarchar(100) COLLATE Latin1_General_100_BIN2 NOT NULL,
    CacheValue nvarchar(2000) NOT NULL,
    ExpiresAt datetime2(7) NOT NULL,
    PRIMARY KEY NONCLUSTERED HASH (CacheKey) WITH (BUCKET_COUNT = 4096),
    INDEX IX_KeyValueCache_Expiry NONCLUSTERED (ExpiresAt)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

The binary collation gives the key a deliberate comparison rule. Normalize keys consistently in the application and validate their maximum length. Do not let truncation create two different logical keys that map to the same stored key.

Size hash buckets according to the expected distinct-key population, then inspect actual chains and memory use. The supplied bucket count is sample setup, not measured capacity guidance. Excess allocation consumes memory while insufficient allocation increases collision chains.

Keep payloads bounded as well. A generic cache accepting arbitrary large documents complicates capacity control and invalidation. Define its supported value format and ownership rather than making it an unexplained shared bag of strings.

Compile Get and Set With Explicit Time Inputs

The native get procedure returns a row only when the key exists and remains valid at the supplied UTC time. Passing time explicitly makes the boundary easy to test. The application must supply a trustworthy current time for ordinary use.

CREATE PROCEDURE dbo.CacheGet
    @CacheKey nvarchar(100),
    @NowUtc datetime2(7)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT CacheValue, ExpiresAt
    FROM dbo.KeyValueCache
    WHERE CacheKey = @CacheKey AND ExpiresAt > @NowUtc;
END;
GO

The native set procedure reads the key into a variable within its atomic transaction. Native modules reject an IF EXISTS subquery here with error 12311, so the variable carries that check. The procedure then updates or inserts the value and expiration. This example intentionally avoids @@ROWCOUNT, whose behavior in native procedures differs from ordinary interpreted procedure statements.

CREATE PROCEDURE dbo.CacheSet
    @CacheKey nvarchar(100),
    @CacheValue nvarchar(2000),
    @ExpiresAt datetime2(7)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    DECLARE @KeyExists bit = 0;
    SELECT @KeyExists = 1 FROM dbo.KeyValueCache WHERE CacheKey = @CacheKey;
    IF @KeyExists = 1
        UPDATE dbo.KeyValueCache
        SET CacheValue = @CacheValue, ExpiresAt = @ExpiresAt
        WHERE CacheKey = @CacheKey;
    ELSE
        INSERT dbo.KeyValueCache(CacheKey, CacheValue, ExpiresAt)
        VALUES (@CacheKey, @CacheValue, @ExpiresAt);
END;
GO

Validate required values, key length, and expiration in the calling boundary. Permissions should expose the intended procedures rather than unrestricted table writes. EXECUTE AS OWNER deserves a security review scoped to this cache's objects.

Concurrent setters can encounter optimistic transaction conflicts or competing inserts for the same key. Handle retryable outcomes with a bounded retry of the complete operation in the caller. Native compilation does not turn this existence check into conflict-free concurrency.

Test Key-Value Cache Expiry and Remove Old Entries

The sample writes one prepared value, reads it before expiry, and tests a time after expiry. Those are expected boundary behaviors rather than observed measurements. The row can remain physically stored while get correctly treats it as expired.

DECLARE @NowUtc datetime2(7) = SYSUTCDATETIME();
DECLARE @ExpiresUtc datetime2(7) = DATEADD(minute,5,@NowUtc);
DECLARE @AfterExpiry datetime2(7) = DATEADD(second,1,@ExpiresUtc);
EXEC dbo.CacheSet N'lookup:100', N'prepared sample', @ExpiresUtc;
EXEC dbo.CacheGet N'lookup:100', @NowUtc;
EXEC dbo.CacheGet N'lookup:100', @AfterExpiry;
DELETE TOP (1000) FROM dbo.KeyValueCache WITH (SNAPSHOT)
WHERE ExpiresAt <= @AfterExpiry;
SELECT @@ROWCOUNT AS RemovedEntries;

The cleanup statement is interpreted T-SQL, so its row-count capture follows ordinary behavior. Schedule bounded cleanup using the real current UTC time in production. The artificial future time above is only for this dedicated sample's expiration test.

An expiry predicate prevents stale reads but does not reclaim every expired row automatically. Monitor the backlog and memory consumption between cleanup runs. Add entry-count and payload limits so successful writes cannot grow the cache without bound.

Test missing keys, refreshed values, exact expiry, and simultaneous writes. Also test restart or planned recovery in an isolated environment and verify repopulation. Do not deliberately restart production merely to demonstrate the non-durable contract.

Compare a Key-Value Cache With Caching in the Application

An application cache can avoid a database round trip for repeated reads. A SQL-side cache remains useful when values are shared among SQL operations or need centralized lookup behavior. Choose placement from access patterns, invalidation ownership, and measured end-to-end cost.

I measure cache misses and rebuild load alongside successful lookups. I also test the cold-start population after recovery. A cache that helps steady-state reads can still create a stampede when every request tries rebuilding the same missing value.

Coordinate rebuilds for expensive values and limit concurrent population work. Decide how a caller behaves when the authoritative source is temporarily unavailable. A miss must have a defined result rather than quietly becoming an empty business value.

Can the application operate correctly with this key-value cache completely empty? Establish that before relying on SCHEMA_ONLY. A key-value cache earns its place when it improves repeated work without becoming another unexplained source of truth.

Deleting expired entries does not instantly reclaim every version held by active transactions. Memory-optimized version cleanup has its own lifecycle. Monitor actual memory use after cleanup rather than assuming removed-row count equals immediately available capacity.

Related reading on this blog: Generate In-Memory OLTP Migration Checklists: SSMS and How to Find the In-Memory OLTP Tables Memory Usage on the Server.

A cache is not authoritative storage, it is replaceable data with a controlled lifetime.

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