sql server in-memory oltp: what every sql professional should know
Post on 12-Apr-2017
84 Views
Preview:
TRANSCRIPT
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.
top related