Creating Memory-Optimized Tables & Indexes

Memory-Optimized Tables

•       Defined as C structs, compiled into DLLs, and loaded into memory

•       Can be persisted as filestreams, or non-durable

•       Do not apply any locking semantics

•       Can be indexed using hash indexes

•       Can co-exist with disk-based tables

•       Can be queried using Transact-SQL

•       Cannot include some data types, including text, image, and nvarchar(max)

•       Do not support identity columns or foreign key constraints

Memory-Optimized Table Scenarios

•       Optimistic concurrency optimizes latch-bound workloads:

–      Multiple concurrent transactions modify large numbers of rows

–      A table contains “hot” pages

•       Applications should handle conflict errors:

–      Write conflicts

–      Repeatable read validation failures

–      Serializable validation failures

–      Commit dependency failures

Creating Memory-Optimized Tables

•       Add a filegroup for memory-optimized data

ALTER DATABASE MyDB

ADD FILEGROUP mem_data CONTAINS MEMORY_OPTIMIZED_DATA;

GO

ALTER DATABASE MyDB

ADD FILE (NAME = 'MemData' FILENAME = 'D:\Data\MyDB_MemData.ndf')

TO FILEGROUP mem_data;

•       Create a memory-optimized table

CREATE TABLE dbo.MemoryTable

(OrderId INTEGER NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000),

 OrderDate DATETIME NOT NULL,

 ProductCode INTEGER NULL,

 Quantity INTEGER NULL)

WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Memory-Optimized Indexes

•       Hash Indexes

–      Rows assigned to buckets based on hashed key

–      Multiple rows in the same bucket form a linked list

–      Effective for equality predicates

–      Query results are not sorted

CREATE TABLE tab1

(col1 INT NOT NULL INDEX h_idx NONCLUSTERED HASH

       WITH (BUCKET_COUNT = 100))

WITH (MEMORY_OPTIMIZED = ON,

      DURABILITY = SCHEMA_ONLY)

•       Range Indexes

–      Latch free, in-memory B-Tree structure

–      Effective for range scans, equality predicates, and inequality predicates

CREATE TABLE tab2

(col1 INT NOT NULL INDEX r_idx NONCLUSTERED)

WITH (MEMORY_OPTIMIZED = ON,

      DURABILITY = SCHEMA_ONLY)

Querying Memory-Optimized Tables

•       Query Interop

–      Interpreted Transact-SQL

–      Enables queries that combine memory-optimized and disk-based tables

•       Native Compilation

–      Stored procedure converted to C and compiled

–      Access to memory-optimized tables only