In-Memory OLTP

39.1 Concepts

Memory-optimized tables: lock-free and latch-free, using multi-version optimistic concurrency.

Natively compiled procedures: T-SQL compiled to machine code.

Durability: SCHEMA_AND_DATA or SCHEMA_ONLY (non-durable, for staging and session state).

39.2 Implementation

Index types: hash (point lookups; size the BUCKET_COUNT correctly) and nonclustered/range (Bw-tree).

Memory-optimized table types (a faster replacement for table variables) and memory-optimized tempdb metadata (2019+).

ALTER DATABASE ShopDB ADD FILEGROUP FG_InMem CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE ShopDB ADD FILE (NAME = 'InMemData', FILENAME = 'D:\Data\InMem') TO FILEGROUP FG_InMem;

CREATE TABLE dbo.SessionState
(
    SessionID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    UserID    INT NOT NULL INDEX IX_User NONCLUSTERED,
    Data      VARBINARY(4000),
    LastSeen  DATETIME2 NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY);

CREATE PROCEDURE dbo.usp_TouchSession @SessionID UNIQUEIDENTIFIER
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    UPDATE dbo.SessionState SET LastSeen = SYSDATETIME() WHERE SessionID = @SessionID;
END