Transcript SQL Server 2014: In In-memory OLTP for Database Developers Enhanced High Availability In-Memory Technologies In-Memory OLTP • 5-25X performance gain for OLTP integrated into SQL Server In-Memory DW • 5-25X performance gain.

SQL Server 2014: In In-memory OLTP for
Database Developers
Enhanced
High Availability
In-Memory
Technologies
In-Memory OLTP
•
5-25X performance gain for
OLTP integrated into SQL
Server
In-Memory DW
•
5-25X performance gain and
high data compression
•
Updatable and clustered
SSD Buffer Pool
Extension
•
4-10X of RAM and up to 3X
performance gain
transparently for apps
Always On
Enhancements
•
Increased availability and
improved manageability of
active secondaries
Online Database
Operations
•
Increased availability for
index/partition maintenance
New Hybrid
Scenarios
Backup to Azure
•
Easy to implement and cost
effective Disaster Recovery
solution to Azure Storage
HA to Azure VM
•
Easy to implement and cost
effective high availability
solution with Windows Azure
VM
Deploy to Azure
• Deployment wizard to migrate
database
Other investments
Better together
with Windows
Server
•
•
•
•
WS2012 ReFS support
Online resizing VHDx
Hyper-V replica
Windows “Blue” support
Extending Power
View
•
Enable Power View on
existing analytic models
and support new multidimensional models.
In-Memory
Technologies
In-Memory OLTP
•
5-25X performance gain for
OLTP integrated into SQL
Server
In-Memory DW
•
5-30X performance gain and
high data compression
•
Updatable and clustered
SSD Buffer Pool
Extension
•
4-10X of RAM and up to 3X
performance gain
transparently for apps
M
•
Market need for higher throughput and predictable lower latency OLTP
at a lower cost
•
Hardware trends demand architectural changes on RDBMS
•
In-Memory OLTP is:
High performance,
Memory-optimized OLTP engine,
Integrated into SQL Server and
Architected for modern hardware trends
Moore’s Law on total CPU processing
power holds but in parallel
processing…
CPU clock rate stalled…
Decreasing RAM cost
$ per GB of PC Class Memory
1000000
10000
1000
100
10
1
1990
1991
1992
1993
1994
1994
1995
1996
1997
1998
1999
2000
2000
2001
2002
2004
2005
2007
2008
2009
2011
US$/GB
100000
Drivers
Hekaton Tech Pillars
Customer
Benefits
M
High performance data
operations
Efficient, business-logic
processing
Frictionless scale-up
Hybrid engine and
integrated experience
Main-Memory
Optimized
T-SQL Compiled to
Machine Code
High Concurrency
SQL Server Integration
• T-SQL compiled to machine
code leveraging VC compiler
• Procedure and its queries,
becomes a C function
• Aggressive optimizations @
compile-time
• Multi-version optimistic
concurrency control with full
ACID support
• Lock-free data structures
• No locks, latches or spinlocks
• No I/O during transaction
•
•
•
•
•
Direct pointers to rows
Indexes exist only in memory
No buffer pool
No write-ahead logging
Stream-based storage
Business
Hardware trends
Steadily declining memory
price, NVRAM
Stalling CPU clock rate
• Same manageability,
administration &
development experience
• Integrated queries &
transactions
• Integrated HA and
backup/restore
Many-core processors
TCO
Demo
Business and User impact
Application
TDS Handler and Session Management
No V1 improvements in
comm layers
20-40x more efficient
Parser,
Catalog,
Optimizer
Hekaton
Compiler
Native-Compiled
SPs and Schema
Checkpoints are
background sequential IO
Execution Plan cache for
ad-hoc T-SQL and SPs
Existing SQL
Component
T-SQL Interpreter
Hekaton
Component
Access Methods
Non-durable
Table
Reduced log contention;
Low latency still critical for
performance
Key
Query
Interop
Hekaton Engine: Memory_optimized
Tables & Indexes
Memory-optimized Table
Filegroup
SQL Server.exe
T1
T2
T3 Tables
T1
T2
T3 Indexes
Buffer Pool
T1
T2
T3
T1
T2
T3
T1
T2
T3
T1
T2
T3
Transaction Log
Data Filegroup
Generated
.dll
Factor X Gains for Applications
Apps that take full advantage: e.g. web
app session state
25
BEST FIT
INGEST/READ
HEAVY
Apps with periodic bulk updates & heavy
random reads
10
Existing apps typically see 4-7x
improvement
5
LEGACY APP
WL DERIVED
FROM TPC-C
2
0
5
10
15
X factor Gains
20
25
30
Despite 20 years of optimizing for
existing OLTP benchmarks – we still get
2x on a workload derived from TPC-C
Hash Index
CREATE TABLE [Customer](
BUCKET_COUNT 1-2X nr of
unique index key values
[CustomerID] INT NOT NULL
PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
[Name] NVARCHAR(250) NOT NULL,
[CustomerSince] DATETIME NULL
Indexes are specified inline
INDEX [ICustomerSince] NONCLUSTERED
)
NONCLUSTERED indexes are
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
supported
This table is
memory optimized
This table is durable
Non-durable tables:
DURABILITY=SCHEMA_ONLY
Demo
Table and Stored
Procedure Performance
Gain
y-o
•
•
•
•
•
•
•
•
•
Row header
Payload (table columns)
8 bytes * (IdxLinkCount)
•
•
•
•
•
•
Begin Ts
End Ts
StmtId
IdxLinkCount
8 bytes
8 bytes
4 bytes
2 + 2 (padding)
bytes
Transaction 99: Running compiled query
SELECT City WHERE Name = ‘John’
Simple hash lookup returns direct pointer to ‘John’ row
Hash index
on Name
50, ∞100
f(John)
John
100, ∞
90, ∞
Timestamps
John
Susan
Chain ptrs
Name
Paris
Prague
Bogota
City
Hekaton Principle:
Background operation will unlink and deallocate the
old ‘John’ row after transaction 99 completes.
• Performance like a cache
• Functionality like a RDMBS
Transaction 100:
UPDATE City = ‘Prague’ where Name = ‘John’
No locks of any kind, no interference with
transaction 99


Page Mapping Table
0
PAGE
1
PAGE

Physical

Logical
Root
2
10
3
5
8
20


PageID-0
28

PageID-3
PageID-2
10
11
15
21
18
24
PageID
1
14
Page size- up to 8K. Sized to the row
Logical pointers
2
6
4
200, ∞
15
100,200
1
Key
1
7
27
Indirect physical pointers through Page
Mapping table
Page Mapping table grows (doubles) as table
grows
Sibling pages linked one direction

Require two indexes for ASC/DSC

Handled thru delta pages or building new
pages
No in-place updates on index pages
No covering columns (only the key is stored)
Non-leaf pages
-14
25
8
50, 300
2
Key
26
27
leaf pages
Data rows
CREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME
WITH
This proc is natively
NATIVE_COMPILATION,
compiled
SCHEMABINDING,
Native procs must be
EXECUTE AS OWNER
schema-bound
AS
Execution context is
BEGIN ATOMIC
required
WITH
(TRANSACTION
Atomic blocks
ISOLATION LEVEL = SNAPSHOT,
• Create a transaction
LANGUAGE = N'us_english')
if there is none
• Otherwise, create a
savepoint
-- insert T-SQL here
END
Session settings are fixed
at create time
Accessing Memory Optimized Tables
Interpreted T-SQL Access
• Access both memory- and diskbased tables
• Less performant
• Virtually full T-SQL surface
When to use
• Ad hoc queries
• Reporting-style queries
• Surface area not available in native
Natively Compiled Procs
• Access only memory optimized tables
• Maximum performance
• Limited T-SQL surface area
When to use
• OLTP-style operations
• Optimize performance critical business
logic
•
•
•
Available
Memory
Memory
Optimized
Tables
Buffer Pool
Memory
Optimized
Tables
Buffer Pool
Memory
Optimized
Tables
Buffer Pool
Memory
Optimized
Tables
Buffer Pool
•
•
•
•
•
•
• Memory-optimized
•
Demo
Memory Usage
and Control
0
100
Checkpoint File Pair
Data File
Transaction Timestamp Range
TS (ins)
TS (ins)
TS (ins)
RowId
RowId
RowId
TableId
TableId
TableId
Data file contains rows inserted
within a given transaction range
Delta File
TS (ins)
TS (ins)
TS (ins)
RowId
RowId
RowId
TS (del)
TS (del)
TS (del)
Delta file contains deleted rows
within a given transaction range
Row pay load
Row pay load
Row pay load
Populating Data/Delta files via sequential IO only
DelDel
Tran1
(row TS150))
Tran1(TS150
•
•
Insert into
Insert into T1
Hekaton T1
Delete 250 TS
New Inserts
Delete 450 TS
Range 500-
•
Delete 150 TS
Range 400-500
•
Del Tran3
(row
250)
(TSTS
250)
Offline Checkpoint Thread
Range 300-400
•
Data file has pre-allocated
size (128 MB or 16 MB on
smaller systems)
Engine switches to new
data file when the current
file is full
Transaction does not span
data files
Once a data file is closed,
it becomes read-only
Row deletes are tracked in
delta file
Files are append only
Del Tran2
(TSTS
450)
(row
450)
Log in disk
Table
Range 200-300
•
Range 100-200
SQL Transaction log
(from LogPool)
Memory-optimized Table Filegroup
Data file with rows generated in timestamp range
IDs of Deleted Rows (height indicates % deleted)
•
•
•
•
•
•
Memory-optimized data Filegroup
Data file with rows generated in
timestamp range
Range 500-600
Range 400-500
Range 200-400
300-399
Range 300-400
Merge
200-400
Range 200-300
200-299
Files as of Time 600
Range 100-200
Range 400-500
Range 300-400
Range 200-300
Range 100-200
Files as of Time 500
Memory-optimized data Filegroup
IDs of Deleted Rows (height
indicates % deleted)
Files Under Merge
Deleted Files
•
•
•
•
•
In-Memory OLTP
Memory
Optimized Tables
Recovery Data
Loader
filter
Recovery Data
Loader
filter
Recovery Data
Loader
filter
Delta map
Delta map
Delta map
Data
File1
Delta
File1
Data
File2
Delta
File2
Memory Optimized Container - 1
Data
File3
Delta
File3
Memory Optimized Container - 2
Optimal for V1
Not Optimal for V1
Business logic
In database (as SPs)
In mid-tier
Latency and contention
locations
Concentrated on a sub-set of
tables/SPs
Spread across the database
Client server communication
Less frequent
Chatty
T-SQL surface area
Basic
Complex
Log IO
Not limiting factor
Limiting factor
Data size
Hot data fit in memory
Unbounded hot data size
Demo
Analysis and Migration tools
SNAPSHOT
REPEATABLE READ
SERIALIZABLE
Time
Transaction T1 (SNAPSHOT)
1
BEGIN
Transaction T2 (SNAPSHOT)
2
BEGIN
3
UPDATE t SET c1=‘value2’ WHERE c2=123
4
UPDATE t SET c1=‘value1’ WHERE
c2=123 (write conflict)
First writer
wins
Disk-based tables
Memory-optimized tables
Tab1
Regular Tx context
Tabn
Tab1
Tabm
Mem-opt Tx context
Supported isolation level combinations (V1)
Disk-based
Memory optimized
Usage recommendations
READCOMMITTED
SNAPSHOT
• Baseline combination – most cases that use
READCOMMITTED today
READCOMMITTED
REPEATABLEREAD/
SERIALIZABLE
• Data migration
• Hekaton-only Interop
REPEATABLEREAD/
SERIALIZABLE
SNAPSHOT
• Memory-optimized table access is INSERT-only
• Useful for data migration and if no concurrent writes on
memory-optimized tables (e.g., ETL)
Unsupported isolation level combinations (V1)
Disk-based
Memory optimized
SNAPSHOT
Any isolation level
REPEATABLEREAD/
SERIALIZABLE
REPEATABLEREAD/
SERIALIZABLE