Kevin FarleeMicrosoft SQL Server

SQL Server 2014: In-Memory Overview OLTP DW and BPEDBI-B332

SQL Server 2014 InvestmentsIn-Memory

Technologies

Enhanced High

Availability

New Hybrid Scenarios

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 Bufferpool 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

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

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 multi-dimensional models.

Other investments

SQL Server 2014 In-Memory TechnologyIn-Memory

Technologies

In-Memory OLTP• 5-20X performance

gain for OLTP integrated into SQL Server

In-Memory DW• 5-25X performance

gain and high data compression

• Updatable and clustered

SSD Bufferpool Extension• 4-10X of RAM and up

to 3X performance gain transparently for apps

Applicable to

Transactional workloads: Concurrent data entry, processing and retrieval

Applicable to

Decision support workloads: Large scans and aggregates

Applicable to

Disk-based transactional workloads:Large working (data)set

OutlineIn-Memory OLTP OverviewMotivating trends – hardware, software, appsIn-memory OLTP architecture & integration into SQL ServerPerformance and customer results

In-Memory Data Warehousing OverviewBuffer Pool Extension Overview

Why In-memory OLTP (aka Hekaton)Customer demand for ever higher throughput and predictable lower latency OLTP at a lower cost

Hardware trends demand architectural changes on RDBMS to meet those demands

In-memory OLTP is: High performance,Memory-optimized OLTP engine, Integrated into SQL Server and Architected for modern hardware

6

1990

1991

1992

1992

1993

1994

1995

1995

1996

1997

1998

1998

1999

2000

2001

2002

2003

2004

2006

2007

2008

2009

2010

1

10

100

1000

10000

100000

1000000

$ per GB of PC Class Memory

US$

/GB

Meanwhile RAM cost

continues to drop

Moore’s Law on total CPU

processing power holds but in

parallel processing…

CPU clock rate stalled…

Because processors

would melt…

Hardware TrendsNew CPU

won’t run a short

transaction much faster

OutlineIn-Memory OLTP OverviewMotivating trends – hardware, software, appsIn-memory OLTP architecture & integration into SQL ServerPerformance and customer results

In-Memory Data Warehousing OverviewBuffer Pool Extension Overview

SQL Server Integration

• Same manageability, administration & development experience

• Integrated queries & transactions

• Integrated HA and backup/restore

Main-Memory Optimized

• Direct pointers to rows

• Indexes exist only in memory

• No buffer pool• No write-ahead

logging• Stream-based

storage

Non-Blocking Execution

• Multi-version optimistic concurrency control, full ACID support

• Lock-free data structures

• No locks, latches or spinlocks,

• No I/O in transaction

T-SQL Compiled to Native

Machine Code• T-SQL compiled

to machine code leveraging VC compiler

• Procedure and its queries, becomes a C function

• Aggressive compile-time optimizations

In-Memory OLTP Architectural Pillars

Arc

hit

ectu

ral P

illa

rsR

esu

lts

Hybrid engine and integrated

experience

In-memory cache speed with

capabilities of a database

Transactions execute to completion

without blocking

Queries & business logic run at native-code speed

Pri

nci

ple

s Performance-critical data fits

in memory

Conflicts are Rare

Push decisions to compilation

timeBuilt-In

Memory-optimized Table

FilegroupData Filegroup

SQL Server.exe

Hekaton Engine: Memory_optimized Tables &

Indexes

TDS Handler and Session Management

In-Memory OLTP: Integration and Application Migration

Native-Compiled SPs and Schema

Buffer Pool

Execution Plan cache for ad-hoc T-

SQL and SPs

Application

Transaction Log

Query Interop

Non-durable Table option

T1

T3

T2

T1

T3

T2

T1

T3

T2

T1

T3

T2

TablesIndexes

T-SQL Interpreter

T1

T3

T2

T1

T3

T2

Access Methods

Parser, Catalog, Optimize

r

Hekaton Compiler Hekaton

Component

KeyExisting

SQL Compone

nt

Generated .dll

20-40x more efficient

Real Apps see 2-30x

Reduced log contention; Low

latency still critical for

performance

Few improvements in

comm layers

Direct Access, Multi-Version, Lock-Free Transactions

50, ∞ John Paris

Timestamps NameChain ptrs City

Hash index on Name

Transaction 100: UPDATE City = ‘Prague’ where Name = ‘John’No locks of any kind, no interference with transaction 99

100, ∞ John Prague

90, ∞ Susan Bogota

f(John)

100

Transaction 99: Running compiled querySELECT City WHERE Name = ‘John’Simple hash lookup returns direct pointer to ‘John’ rowBackground operation will unlink and deallocate the old ‘John’ row after transaction 99 completes.

Create Table DDLCREATE TABLE [Customer]( [CustomerID] INT NOT NULL

PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), [Name] NVARCHAR(250) NOT NULL, [CustomerSince] DATETIME NULL

INDEX [ICustomerSince] NONCLUSTERED INDEX [ICustomerID-Since] NONCLUSTERED (CustomerID, CustomerSince)

)WITH (MEMORY_OPTIMIZED = ON,

DURABILITY = SCHEMA_AND_DATA);

This table is memory optimized

This table is durable.Non-durable tables:

DURABILITY=SCHEMA_ONLY

Indexes may be specified inlineNONCLUSTERED indexes are

supported

Hash IndexBUCKET_COUNT

Use 2x # of unique index key

values

Multi-Column indexes are supported

Limitations on Memory Optimized TablesOptimized for high-throughput OLTPNo XML, CLR data types and sqlvariant

Optimized for in-memoryRows are at most 8060 bytes – no off row data (for SQL Server 2014)No Large Object (LOB) types like varchar(max) (for SQL Server 2014)

Other limitationsNo FOREIGN KEY and no CHECK constraints (for SQL Server 2014)No schema changes (ALTER TABLE, add/drop index) (for SQL Server 2014)No DML triggers (for SQL Server 2014)

Create Procedure DDLCREATE PROCEDURE [dbo].[InsertOrder] @id INT, @date DATETIME WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNERAS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')

-- insert T-SQL hereEND

This proc is natively compiled

Native procs must be schema-bound

Atomic blocks • Create a

transaction if there is none

• Otherwise, create a savepoint

Execution context is required

Session settings are fixed at create time

Durability and AvailabilityOne of the hardest parts of a high-performance in-memory system

Performance goals require in-memory format to diverge from storage format

Storage format must be very efficient for checkpoint and recoveryContention on the transaction log grows

High-availability remains very critical

OutlineIn-Memory OLTP OverviewMotivating trends – hardware, software, appsIn-memory OLTP architecture & integration into SQL ServerPerformance and customer results

In-Memory Data Warehousing OverviewBuffer Pool Extension Overview

Customer ExperiencesCustomer Workload Results

Edgenet – SaaS provider for retailers and product delivery for end consumers

Provide real-time insight into product price/availability for retailers and end-consumers.

Used by retailers in-stores and to end-consumers via search engines.

•8x-11x in performance gains for ingestion of data•Consolidated from multi-tenant, multi-server to single database/server.•Removed application caching layer (additional latency) from client tier. Case Study

BWin.party - Largest regulated online gaming site

Running ASP.NET session state using SQL Server for repository    

Critical to end-user performance and interaction with the site

•Increase 15,000 batch requests/sec per to over 250,000 in case study•Lab testing achieved over 450,000 batch requests/sec•Consolidate 18 SQL Servers -> 1. Case Study

SBI Liquidity Market - Japanese foreign currency exchange trading platform.

Includes high volume and low latency trading.

Expecting 10x volume increase

•2x throughput (get over 3x in testing) with goal to reduce latency from 4 seconds to 1 per business transaction.•Case study

Early Customer Performance Gains

Update Heavy OLTP

Legacy App

Ingest / Read heavy

Best Fit

0 5 10 15 20 25 30

2

5

10

25

Factor X Gains for Appli-cations

X factor Gains

Despite 20 years of optimizing for OLTP benchmarks – we still get 2x on a workload derived

from TPC-C

Apps that take full advantage: e.g. web app session state

Apps with periodic bulk updates & heavy random reads

Existing apps typically see 4-7x improvement

Characteristics of In-Memory ArchitecturesOptimized for efficient CPU processingInstead of being optimized for efficient buffering of storageGoal: keep CPUs well fed with data

Storage and memory formats differAt least until NVRAM becomes commonplace

Immutable in-memory data and on-disk structuresInstead of highly mutable complex on-disk structures

I/O done in bulk (streaming)Instead of random I/O

Optimistic concurrency controlLogical conflicts rarePhysical conflicts rare, but contention magnified with higher throughput enabled by in-memory efficiency

Apply to

In-Memory BI(PowerPivot, PowerView)

In-Memory DW (columnstore)

In-Memory OLTP

Suitable Application CharacteristicsApplication is suited for in-memory processing

All performance critical data already fits in memoryTransaction locking or physical latching causing stalls and blocking

Application is “OLTP-Like”Relatively short-lived transactionsHigh degree of concurrent transactions from many connectionsExamples: Stock trading, travel reservations, order processing

Application migration simplified ifStored procedures usedPerformance problems isolated to subset of tables and SPs

Production CustomersCustomer App Pattern SQL Common Labs Event Reporting Shock Absorber BWIN - Session State Relational Cache Saxobank Read Scale Baltika Breweries OLTP Edgenet Read Scale Derivco Relational Cache SBI Liquidity OLTPPlenty Of Fish Read Scale CareGroup Shock Absorber

OutlineIn-Memory OLTP OverviewMotivating trends – hardware, software, appsIn-memory OLTP architecture & integration into SQL ServerPerformance and customer results

In-Memory Data Warehousing OverviewBuffer Pool Extension Overview

In-memory DW (ColumnStore)Typical scenarios

Data is typically written once and read many timesQueries are against large amounts of dataAggregating, filtering are common

Star & snowflake schemas are common using dimension and fact tables

New data is typically appended to fact tables

Don't Know

More than 10 TB

3 - 10 TB

1 - 3 TB

Less than 1TB

0% 10% 20% 30% 40% 50%

6%

34%

25%

18%

17%

2%

17%

19%

21%

41%

Approximate data volume managed by DW

Today

In 3 years

In-memory DW overview

…

C1 C2 C3 C5C4

Benefits:• Improved

compression:Data from same domain compress better

• Reduced I/O:Fetch only columns needed

• Improved Performance:More data fits in memory

Data stored as rows

Columnstore internals

Data stored as columns

OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount

20101107 106 01 1 6 30.00

20101107 103 04 2 1 17.00

20101107 109 04 2 2 20.00

20101107 103 03 2 1 17.00

20101107 106 05 3 4 20.00

20101108 106 02 1 5 25.00

20101108 102 02 1 1 14.00

20101108 106 03 2 5 25.00

20101108 109 01 1 1 10.00

20101109 106 04 2 4 20.00

20101109 106 04 2 5 25.00

20101109 103 01 1 1 17.00

ColumnStore Index Example

OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount

20101107 106 01 1 6 30.00

20101107 103 04 2 1 17.00

20101107 109 04 2 2 20.00

20101107 103 03 2 1 17.00

20101107 106 05 3 4 20.00

20101108 106 02 1 5 25.00

OrderDateKey ProductKey StoreKey RegionKey Quantity SalesAmount

20101108 102 02 1 1 14.00

20101108 106 03 2 5 25.00

20101108 109 01 1 1 10.00

20101109 106 04 2 4 20.00

20101109 106 04 2 5 25.00

20101109 103 01 1 1 17.00

1. Horizontally Partition (create Row Groups)

~1M rows

OrderDateKey

20101107

20101107

20101107

20101107

20101107

20101108

ProductKey

106

103

109

103

106

106

StoreKey

01

04

04

03

05

02

RegionKey

1

2

2

2

3

1

Quantity

6

1

2

1

4

5

SalesAmount

30.00

17.00

20.00

17.00

20.00

25.00

OrderDateKey

20101108

20101108

20101108

20101109

20101109

20101109

ProductKey

102

106

109

106

106

103

StoreKey

02

03

01

04

04

01

RegionKey

1

2

1

2

2

1

Quantity

1

5

1

4

5

1

SalesAmount

14.00

25.00

10.00

20.00

25.00

17.00

2. Vertically Partition (create Segments)

OrderDateKey

20101107

20101107

20101107

20101107

20101107

20101108

ProductKey

106

103

109

103

106

106

StoreKey

01

04

04

03

05

02

RegionKey

1

2

2

2

3

1

Quantity

6

1

2

1

4

5

SalesAmount

30.00

17.00

20.00

17.00

20.00

25.00

Some segments will compress more than others

OrderDateKey

20101108

20101108

20101108

20101109

20101109

20101109

ProductKey

102

106

109

106

106

103

StoreKey

02

03

01

04

04

01

RegionKey

1

2

1

2

2

1

Quantity

1

5

1

4

5

1

SalesAmount

14.00

25.00

10.00

20.00

25.00

17.00

*Encoding and reordering not shown

3. Compress Each Segment

OrderDateKey

20101107

20101107

20101107

20101107

20101107

20101108

ProductKey

106

103

109

103

106

106

StoreKey

01

04

04

03

05

02

RegionKey

1

2

2

2

3

1

Quantity

6

1

2

1

4

5

SalesAmount

30.00

17.00

20.00

17.00

20.00

25.00OrderDateKey

20101108

20101108

20101108

20101109

20101109

20101109

ProductKey

102

106

109

106

106

103

StoreKey

02

03

01

04

04

01

RegionKey

1

2

1

2

2

1

Quantity

1

5

1

4

5

1

SalesAmount

14.00

25.00

10.00

20.00

25.00

17.00

4. Read The Data

Column Elimination Seg

men

t Elim

inati

on

SELECT ProductKey, SUM (SalesAmount) FROM SalesTable WHERE OrderDateKey < 20101108

Updatable Columnstore Index

Table consists of column store and row storeDML (update, delete, insert) operations leverage delta store

INSERT ValuesAlways lands into delta store

DELETELogical operationData physically remove after REBUILD operation is performed.

UPDATEDELETE followed by INSERT.

BULK INSERTif batch < 100k, inserts go into delta store, otherwise columnstore

SELECT Unifies data from Column and Row stores - internal UNION operation.

“Tuple mover” converts data into columnar format once segment is full (1M of rows)REORGANIZE statement forces tuple mover to start.

C1 C2 C3 C5 C6C4

Colu

mn

Sto

re

C1 C2 C3 C5 C6C4

Delt

a (

row

)st

ore

tup

le m

over

OutlineIn-Memory OLTP OverviewMotivating trends – hardware, software, appsIn-memory OLTP architecture & integration into SQL ServerPerformance and customer results

In-Memory Data Warehousing OverviewBuffer Pool Extension Overview

SSD bufferpool extension: motivation

RANDOM IOPS to Mechanical Drives IOPS Offload to SSD

SSD buffer pool extension - summary

Use of cheaper SSD to reduce SQL memory pressureNo risk of data loss – only clean pages are involvedObserved up to 3x performance improvement for OLTP workloads during the internal testingSweet spot machine size:

Up to 32GB RAMHigh throughput and endurance SSD storage (ideally PCI-E) sized 4x-10x times of RAM size

Not optimized for DW workloads *Not applicable to In-memory OLTP

* DW workload data set is so large that table scan will wash out all the cached pages.

Towards Seamless Integration in SQL 2014 SQL Server RDBMS

Column-Store Engine

for DW

Disk-optimized, row-store

engine

Memory-optimized

OLTP engine

AlwaysOn, Backup/Restore, Security, Admin, Deployment

Transactions, Queries, Stored Procedures

Batch Query

Operators

Native-Compiled

ProceduresBlobs

Readable Secondaries

Range + Column secondary

indexes

Nearly seamless across all engines

Work across all engines but

with limitations

Work across two engines

with limitations

Available only in one engine

Not comprehensive - only representative examples.

DBI-333 In-Memory Overview

DBI-333 In-Memory OLTP Customer Deployments

Related content

Microsoft Solutions Experience Location (MSE)

27 Hands on Labs + 8 Instructor Led Labs in Hall 7

DBI Track resources

Free SQL Server 2014 Technical Overview e-book

microsoft.com/sqlserver and Amazon Kindle StoreFree online training at Microsoft Virtual Academy

microsoftvirtualacademy.com Try new Azure data services previews!Azure Machine Learning, DocumentDB, and Stream Analytics

Resources

Learning

Microsoft Certification & Training Resources

www.microsoft.com/learning

Developer Network

http://developer.microsoft.com

TechNet

Resources for IT Professionals

http://microsoft.com/technet

Sessions on Demand

http://channel9.msdn.com/Events/TechEd

Please Complete An Evaluation FormYour input is important!TechEd Schedule Builder CommNet station or PC

TechEd Mobile appPhone or Tablet

QR code

Evaluate this session

© 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows, and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.