SQL Server In-Memory OLTP:What Every SQL Professional Should KnowBob WardPrincipal ArchitectMicrosoft, Data Group, Tiger Team

http://aka.ms/bobsqlbobward@microsoft.com@bobwardms, #bobsql

Latch free since 2007

Decks and demos at http://aka.ms/bobwardms

Let’s Do This

The

What

and Why

How do I use this? How does this work?

Based on SQL Server 2016

The What and Why

ἑκατόν

The Research behind the concept• OLTP Through the Looking G

lass, and What We Found There

by Stavros Harizopoulos, Daniel J. Abadi, Samuel Madden, Michael Stonebraker , SIGMOD 2008

• TPC-C hand-coded on top of the SHORE storage engine

The Path to In-Memory OLTP

Project Verde• 2007

“Early” Hekaton• 2009/2010

Hekaton becomes In-Memory OLTP• SQL Server

2014

In-Memory OLTP Unleashed• SQL Server

2016

Keep SQL Server relevant in a world of high-speed hardware with dense cores, fast I/O, and inexpensive massive memory

The need for high-speed OLTP transactions at microsecond speed

Reduce the number of instructions to execute a transaction• Find areas of latency for a transaction and reduce its

footprint• Use multi-version optimistic concurrency and “lock free”

algorithms• Use DLLs and native compiled procs

Why In-Memory OLTP?

XTP = eXtreme Transaction ProcessingHK = Hekaton

Demo: Just Show Us!

See In-Memory OLTP in action

What is it Really?Memory Optimized FILEGROUP

Checkpoint Files

Memory-Optimized Tables

Natively Compiled T-SQL

Stored Procedure

Dynamic Linked

Library (DLL)

SQLSERVR.EXEQuery Processor

Storage Engine

SQLOS

HA

HK Runtime

HK Compile

HK Engine

“Feels” like normal disk-based tables but in memory

Internally completely different

Hash or non-clustered index choices (at least one required)

Max performance

Latch and lock free

Engine within an Engine

• Memory• All data stored in memory separate from Buffer Pool• No “paging” to disk like Buffer Pool under memory pressure• Table data uses different internal structure (no database pages)

• I/O• Transaction logging only at COMMIT• Separate checkpoint processing only used to speed startup• None required for SCHEMA_ONLY tables

• Concurrency• No locking or latching required• Optimistic (conflict error handling may be required)• Versioning used (but not stored in tempdb)

How is this different?

All data loaded into memory at

database startup

Can be controlled with Resource

Governor

How do I use it?

Create a Memory

Optimized FILEGROUP

Decide on an index strategy

Create a Memory-

Optimized Table

Create a natively compiled

stored procedure

Start running transactions

Tune and manage

The Process

• Estimating Table, Index, Versioning, and Growth. Read hereDo I have enough Memory?

• Hash = unique row lookup• Non-clustered = range lookups (also called range index)

Hash or Non-clustered index?

• SCHEMA_ONLY = Does not persist server restarts; No I/O (even log)

• SCHEMA_AND_DATA = Persists server restartsDurability

• Ultimate speed vs some limits• Limited diagnostics by default

Use Natively Compiled Stored

Procedures?• Transaction Log - Transactions only as fast as commit speed• FILEGROUP – One or multiple folders across drives for

faster startupPhysical File Placement

Decisions

Take a look at NVDIMM

• Transaction Performance Analysis Report

• Memory Optimization Advisor• Native Compilation Advisor

Migrating Existing Data

Analyze based on DMVs

Recommend changes

Find incompatibiliti

es

Migrate schema, data,

and procs

• High-throughput and low-latency transaction processing

• Data Ingestion and IoT• Caching and Session State• Tired of Tempdb?• Staging tables for ETL

In-Memory OLTP Scenarios

Demo: Tempdb vs Memory-Optimized Tables

• Locks the table• Maintain indexes• Used to maintain bucketcount for hash indexes• Natively Compled Procedures are SCHEMABOUND

ALTER memory

optimized tables

• Auto Update statistics supported (requires db compat level = 130)• Natively Compiled Stored Procedures not recompiled on auto status

updateMaintaining

Statistics

• Need to reduce space? Delete rows or drop tables• Bind to your own RG resource pool with sp_xtp_bind_db_resource_pool

Managing Memory Usage

Management Considerations

• Supported on Always On Availability Groups• Supports ColumnStore Indexes for HTAP applications• Supported in Azure SQL Database• Cross container transactions (disk and in-memory in one

transaction)• Table variables supported• SQL surface area expanded in SQL Server 2016. Complete

support here• LOB data types (ex. Varchar(max)) supported• BACKUP/RESTORE complete functionality• Memory only limited by the OS (24TB in Windows Server

2016)

In-Memory OLTP Capabilities

How does it work?

• The Hekaton Engine• A pool of threads for garbage collection, checkpoint I/O and Log flush• Compilation of schema and Natively Compiled Procedure DLLs• Engine runtime that is agnostic to table and row formats• The HK Engine code is lock, latch, and spinlock free

Multi-Version Optimistic Concurrency

Architecture 101

Immutable• Rows never change: UPDATE =

DELETE + INSERT

Versions• UPDATE and DELETE create

versions• Timestamps in rows for visibility

and transactions correctness

Optimistic• Assume no conflicts• Snapshots + conflict detection• Guarantee correct transaction

isolation at commit

NOT in tempdb

Pessimistic = locks

Errors may occur

“Normal” INSERTThe Path of In-Memory OLTP Transactions

Release latches and locks

Update index pages ( more locks and latch )

Modify page

Latch page

Obtain locks

INSERT LOG record

In-Memory OLTP INSERT

Maintain index in memory

Insert ROW into memory

COMMIT Transaction = Log Record and Flush

COMMIT Transaction = Insert HK Log Record and

Flush

Page Split

Sys Tran = Log Flush

Spinlocks

No index logging

SCHEMA_ONLY no logging

No latchNo spinlock

• Why CHECKPOINT?• Speed up database startup. Otherwise we would have to keep around a huge tlog around.• We only write data based on committed log records• Independent of SQL recovery intervals or background tasks (1.5Gb log increase)

• All data written in pairs of data and delta files• Preemptive tasks using WriteFile (async with FILE_FLAG_NO_BUFFERING)• Data is always appended• I/O is continuous but CHECKPOINT event is based on 1.5Gb log growth• Instant File Initialization matters for PRECREATED files• Data = INSERTs and UPDATEs• Delta = filter for what rows in Data are actually deleted• Periodically we will MERGE several Data/Delta pairs.

CHECKPOINT and Memory Optimized Tables Log truncation

eligible at CHECKPOINT event

No WAL protocol

Typically 128Mb but can

be 1Gb

Typically 8Mb but can be

128Mb

Read details from database Boot Page (DBINFO)

Load ROOT file for system tables and file metadata

Load ACTIVE DATA files filtered

by DELTA streamed in

parallel

Redo COMMITED transactions

greater than last CHECKPOINT LSN

The Recovery Process =

Want a 30x boost in speed for OLTP?

Transactions are truly lock, latch, and spinlock free

Natively complied stored procedures for super speed

Combine with Columnstore for true HTAP applications

SQL Server 2016 is a major step up from 2014

The Case for In-Memory OLTP

• SQL Server In-Memory OLTP Internals for SQL Server 2016

• In-Memory OLTP Videos: What it is and When/How to use it

• Explore In-Memory OLTP architectures and customer case studies

• In-Memory OLTP in Azure SQL Database Blog• In-Memory OLTP (In-Memory Optimization) docs

Resources

Decks and demos at http://aka.ms/bobwardms

Inside In-Memory OLTP SQL 2016 PASS Summit talk

Follow @jdebruijn

© Copyright Microsoft Corporation. All rights reserved.