Session State in SQL Server With Memory-Optimized Tables

Busy session storage deserves attention even when each request reads just one small value. Memory-optimized session state changes the storage and execution path, but it still needs expiry, concurrency, and recovery rules.

Decide What Session State Can Lose

SCHEMA_ONLY preserves table definitions without persisting their rows for database recovery. A restart, recovery event, or availability failover can leave the transient table empty. The application must handle that outcome as a supported state.

Transient preferences and reconstructable request context differ from orders, payments, and required authentication records. Keep durable business facts in durable storage. Do not put an authoritative checkout decision into a table whose contents can disappear by design.

I decide the empty-store behavior before selecting memory optimization for session state. I also distinguish a missing session from an expired session in application handling. Fast access provides little comfort when recovery loses a required business fact.

Choose SCHEMA_AND_DATA when the required rows must survive database recovery. That changes logging, storage, and operational behavior. It does not remove the need for expiry or a supported session lifecycle.

Prepare the Database and Memory Filegroup

The example targets SQL Server 2025 on Windows with In-Memory OLTP support. It creates a new isolated demonstration database. Confirm that the database name and memory-container path are unused before running the setup.

The C:\SqlData parent directory must already exist, with appropriate service-account permissions. SQL Server creates the named container during the operation. Replace the path with an approved local data location on the test computer.

USE master;
GO
IF COALESCE(CONVERT(int, SERVERPROPERTY('IsXTPSupported')), 0) <> 1
    THROW 50001, 'This instance does not support In-Memory OLTP.', 1;
IF DB_ID(N'SessionStateDemo') IS NOT NULL
    THROW 50002, 'Choose a new isolated demonstration database.', 1;
CREATE DATABASE SessionStateDemo;
GO
ALTER DATABASE SessionStateDemo
    ADD FILEGROUP SessionStateMemory CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE SessionStateDemo
    ADD FILE
    (NAME = N'SessionStateContainer',
     FILENAME = N'C:\SqlData\SessionStateContainer')
    TO FILEGROUP SessionStateMemory;
GO
USE SessionStateDemo;
GO
CREATE TABLE dbo.AppSession
(
    SessionId varchar(64) COLLATE Latin1_General_100_BIN2 NOT NULL,
    SessionData nvarchar(2000) NOT NULL,
    ExpiresUtc datetime2(7) NOT NULL,
    CONSTRAINT PK_AppSession PRIMARY KEY NONCLUSTERED HASH (SessionId)
        WITH (BUCKET_COUNT = 8192),
    INDEX IX_AppSession_Expiry NONCLUSTERED (ExpiresUtc)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);
GO

A memory-optimized filegroup is required even for this transient table. SCHEMA_ONLY does not remove the setup requirement. Check existing filegroups first when adapting the example to a database that already uses memory optimization.

The hash index supports equality lookup by session identifier. Its bucket count is a starting configuration, not a measured ideal for every application. The ordered expiry index supports cleanup predicates with different access needs.

Keep values bounded and estimate active sessions before deployment. Memory use includes indexes and row versions, not only payload bytes. Expired rows still consume resources until deletion and subsequent version cleanup occur.

Compile a Read That Enforces Expiry

The read procedure returns only an unexpired session. The application supplies a UTC timestamp from the trusted service handling the request. An empty result therefore means the session cannot be used under this read contract.

CREATE PROCEDURE dbo.AppSessionRead
    @SessionId varchar(64) NOT NULL,
    @NowUtc datetime2(7) NOT NULL
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT SessionId, SessionData, ExpiresUtc
    FROM dbo.AppSession
    WHERE SessionId = @SessionId AND ExpiresUtc > @NowUtc;
END;
GO

Natively compiled procedures require schema binding and an atomic block with supported options. Create each procedure in its own batch. Explicit column lists also preserve the native module's supported query shape.

UTC timestamps avoid interpreting expiry through different local time zones. Define whether expiry is fixed or slides after approved activity. Do not refresh the expiry silently on every diagnostic read.

Application authorization remains separate from finding a session identifier. Use an unpredictable identifier and verify the request's allowed session context. A fast lookup is not permission for any caller to retrieve another user's state.

Compile a Write with a Clear Conflict Policy

The write procedure inserts a new session or updates an existing one. It validates that expiry lies after the supplied current timestamp. The application must also validate identifier and payload lengths before binding these bounded parameters.

CREATE PROCEDURE dbo.AppSessionWrite
    @SessionId varchar(64) NOT NULL,
    @SessionData nvarchar(2000) NOT NULL,
    @ExpiresUtc datetime2(7) NOT NULL,
    @NowUtc datetime2(7) NOT NULL
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
    (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    IF @ExpiresUtc <= @NowUtc
        THROW 50003, 'Session expiry must be in the future.', 1;
    UPDATE dbo.AppSession
    SET SessionData = @SessionData, ExpiresUtc = @ExpiresUtc
    WHERE SessionId = @SessionId;
    IF @@ROWCOUNT = 0
        INSERT dbo.AppSession(SessionId, SessionData, ExpiresUtc)
        VALUES (@SessionId, @SessionData, @ExpiresUtc);
END;
GO

Two concurrent writers can encounter optimistic concurrency conflicts or competing inserts. The primary key protects identifier uniqueness, but does not eliminate retry handling. Use a bounded application retry for the complete failed operation.

A retry must preserve the application's update semantics. Two requests replacing a whole state document can overwrite each other's logical changes. Use a versioned update contract when lost updates are unacceptable.

A natively compiled procedure cannot place a subquery inside IF EXISTS. That is why this one updates first and then checks @@ROWCOUNT. Native procedures set @@ROWCOUNT, so the insert runs only when no row matched.

Test Session State Expiry and Remove Old Rows

The following sample writes a reconstructable preference value and reads it within its lifetime. A later read uses a timestamp beyond expiry to test the contract. These are expected demonstrations, not observed production timings.

DECLARE @NowUtc datetime2(7) = SYSUTCDATETIME();
DECLARE @ExpiresUtc datetime2(7) = DATEADD(minute, 20, @NowUtc);
EXEC dbo.AppSessionWrite
    @SessionId = 'DEMO_SESSION_01',
    @SessionData = N'{"locale":"en-US"}',
    @ExpiresUtc = @ExpiresUtc, @NowUtc = @NowUtc;
EXEC dbo.AppSessionRead
    @SessionId = 'DEMO_SESSION_01', @NowUtc = @NowUtc;
DECLARE @AfterExpiry datetime2(7) = DATEADD(second, 1, @ExpiresUtc);
EXEC dbo.AppSessionRead
    @SessionId = 'DEMO_SESSION_01', @NowUtc = @AfterExpiry;

Expiry filtering does not delete the row. Run a separate bounded cleanup process on the primary database. Small deletion batches reduce the amount of work attempted in one operation.

DECLARE @CutoffUtc datetime2(7) = SYSUTCDATETIME();
DELETE TOP (1000)
FROM dbo.AppSession WITH (SNAPSHOT)
WHERE ExpiresUtc <= @CutoffUtc;
SELECT @@ROWCOUNT AS DeletedRows;

This cleanup batch uses interpreted T-SQL, so its immediate @@ROWCOUNT capture has the ordinary meaning. It does not promise immediate memory reclamation from every deleted version. Monitor cleanup progress and memory pressure separately.

Define logout behavior too, including removing or invalidating the named session. Cleanup must not extend expired lifetimes merely to improve a hit-rate graph. A session store needs a complete lifecycle rather than only an insert procedure.

Verify Recovery, Access, and End-to-End Benefit

What should the next request do when every transient session disappears after recovery? Exercise that condition in the isolated database and observe the application response. Reauthentication or reconstruction must follow the actual security and business contract.

Grant the application only the required procedure permissions, with authorization enforced in its supported request path. Avoid direct table access for arbitrary clients. EXECUTE AS OWNER is an execution context choice that still requires review.

Compare contention, CPU, memory, and end-to-end latency with the existing implementation. Memory optimization does not remove the network round trip to SQL Server. Test representative concurrency and retries before claiming a benefit.

Keep administrative diagnostics brief when inspecting sensitive state. Log session identifiers only under the approved privacy policy, and avoid logging complete payloads. Use aggregate operational counts when detailed values are unnecessary for diagnosis.

Test expiry at the exact boundary as well as before and after it. The read uses a strict greater-than comparison, so equality counts as expired. Consistent boundary handling prevents two application components from disagreeing about whether the same session remains valid.

Document active-session limits, cleanup ownership, expiry policy, and recovery behavior. Session state belongs here when its lifecycle matches the chosen durability. Retain durable facts elsewhere when the application requires them to survive.

Related reading on this blog: Memory Optimized Tables, Transactions, Isolation Level and Error and Attach an In-Memory Database with T-SQL.

A fast session table is not a session lifecycle, it is storage that needs expiry, concurrency, authorization, and recovery rules.

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