Memory Optimized Tables (MOT) - DB Level

1 Terms 1

2 Overview 1

3 Implementation of MOT 1

3.1 Ensure compatibility level >= 130A 1

3.2 Elevate to SNAPSHOT (Transaction Isolation Level) 1

3.3 Create an optimized FILEGROUP 2

3.4 Create a memory-optimized table 2

3.5 Create a natively compiled stored procedure (native proc) 2

1. Terms

WITH

(MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

NATIVE_COMPILATION,  

SCHEMABINDING  

2. Overview

• https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/introduction-to-memory-optimized-tables?view=sql-server-ver16

• Applies to:  SQL Server /  Azure SQL Database /  Azure SQL Managed Instance

• A technique used to pre-load the table data into memory by using "filestream" files.

• Advantages

• Faster indexing

• Faster access to row level data

• Faster calculations and aggregations

• Faster DML operations

3. Implementation of MOT

1. Ensure compatibility level >= 130A

SELECT d.compatibility_level

    FROM sys.databases as d

    WHERE d.name = Db_Name();

ALTER DATABASE CURRENT

    SET COMPATIBILITY_LEVEL = 130;

2. Elevate to SNAPSHOT (Transaction Isolation Level)

ALTER DATABASE CURRENT

    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

3. Create an optimized FILEGROUP

ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod CONTAINS MEMORY_OPTIMIZED_DATA

ALTER DATABASE imoltp ADD FILE (name='imoltp_mod1', filename='c:\data\imoltp_mod1') TO FILEGROUP imoltp_mod  

4. Create a memory-optimized table

CREATE TABLE dbo.SalesOrder

    (

        SalesOrderId   integer   not null   IDENTITY

            PRIMARY KEY NONCLUSTERED,

        CustomerId   integer    not null,

        OrderDate    datetime   not null

    )

        WITH

            (MEMORY_OPTIMIZED = ON,

            DURABILITY = SCHEMA_AND_DATA);

5. Create a natively compiled stored procedure (native proc)