Enhanced High Availability InMemory Technologies InMemory OLTP 5

Enhanced High Availability In-Memory Technologies In-Memory OLTP • 5 -25 x performance gain for OLTP integrated into SQL Server Always On Enhancements • In-Memory DW • • 5 -25 x performance gain and high data compression Updatable and clustered SSD Bufferpool Extension • 4 -10 X of RAM and up to 3 X performance gain transparently for apps Increased availability and improved manageability of active secondaries Online Database Operations • Increased availability for index/partition maintenance New Hybrid Scenarios Other investments Backup to Azure Better together with Windows Server • 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 • • WS 2012 Re. FS 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 -20 X performance gain for OLTP integrated into SQL Server In-Memory DW • • 5 -25 X performance gain and high data compression Updatable and clustered SSD Bufferpool Extension • 4 -10 X of RAM and up to 3 X 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

Motivating trends – hardware, software, apps

6

Moore’s Law on total CPU processing power holds but in parallel processing… CPU clock rate stalled… Because processors would melt… New CPU won’t run a short transaction much faster Meanwhile RAM cost continues to drop 1000000 10000 100 10 1 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2004 2005 2007 2008 2009 2011 US$/GB $ per GB of PC Class Memory

In-memory OLTP architecture & integration into SQL Server

Principles Performance-critical data fits in memory Architectural Pillars Main-Memory Optimized Direct pointers to rows Indexes exist only in memory No buffer pool No write-ahead logging Stream-based storage 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 compiletime optimizations In-memory cache speed with capabilities of a database Queries & business logic run at nativecode speed • • Results • Push decisions to compilation time Conflicts are Rare • • 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 Transactions execute to completion without blocking Built-In SQL Server Integration • Same manageability, administration & development experience • Integrated queries & transactions • Integrated HA and backup/restore Hybrid engine and integrated experience

Application TDS Handler and Session Management Few improvements in comm layers 20 -40 x more efficient Real Apps see 2 -30 x Reduced log contention; Low latency still critical for performance Parser, Catalog, Optimizer Hekaton Compiler Native. Compiled SPs and Schema Key Execution Plan cache for ad-hoc T-SQL and SPs Existing SQL Component T-SQL Interpreter Hekaton Component Access Methods Non-durable Table option Query Interop Hekaton Engine: Memory_optimized Tables & Indexes Memory-optimized Table Filegroup SQL Server. exe T 1 T 2 T 3 Indexes Tables Buffer Pool T 1 T 2 T 3 Transaction Log Data Filegroup Generated. dll

Timestamps BW-tree index on Name 200, ∞ Chain ptrs Name John J City Susan S Susan John Paris Jane Prague Bogota 50, ∞ 70, 90 Hash index on City B P Beijing 100, 200 90, 150 Row format Brussels Rows are multi-versioned, each has a valid time range indicated by two timestamps A version is visible if transaction read time falls within the version’s valid time Row can be part of multiple indexes, but there is only a single copy of the row Hash indexes accelerate point-lookup BW-tree is a lock-free b-tree (range index) invented by MSR Indexes are not stored on disk

Hash Index BUCKET_COUNT Use 2 x # of unique index key values CREATE TABLE [Customer]( [Customer. ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000), [Name] NVARCHAR(250) NOT NULL, Indexes may be specified inline [Customer. Since] DATETIME NULL NONCLUSTERED indexes are supported INDEX [ICustomer. Since] NONCLUSTERED INDEX [ICustomer. ID-Since] NONCLUSTERED (Customer. ID, Customer. Since) ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA); Multi-Column indexes are supported This table is memory optimized This table is durable. Non-durable tables: DURABILITY=SCHEMA_ONLY

CREATE PROCEDURE [dbo]. [Insert. Order] @id INT, @date DATETIME WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english') -- insert T-SQL here END This proc is natively compiled Native procs must be schema-bound Execution context is required Atomic blocks • Create a transaction if there is none • Otherwise, create a savepoint Session settings are fixed at create time

One 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 recovery Contention on the transaction log grows High-availability remains very critical

Performance and customer results

Customer Workload Results Edgenet – Saa. S provider for Provide real-time insight into retailers and product price/availability for delivery for end consumers retailers and end-consumers. • 8 x-11 x in performance gains for ingestion of data • Consolidated from multi-tenant, multi -server to single database/server. Used by retailers in-stores and to • Removed application caching layer end-consumers via search engines. (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 SBI Liquidity Market Includes high volume and low Japanese foreign currency latency trading. exchange trading platform. Expecting 10 x volume increase • 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 • 2 x throughput (get over 3 x in testing) with goal to reduce latency from 4 seconds to 1 per business transaction. • Case study

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 5 Legacy App Update Heavy OLTP Existing apps typically see 4 -7 x improvement 2 0 10 X factor Gains 20 30 Despite 20 years of optimizing for OLTP benchmarks – we still get 2 x on a workload derived from TPC-C

Transaction CPU cycles (in millions) Speedup size in Memory-Opt #lookups Regular 1 0. 73 0. 040 10. 8 x 10 0. 93 0. 051 18. 4 x 100 2. 72 0. 150 18. 1 x 1, 000 20. 10 1. 063 18. 9 x 10, 000 201. 00 9. 850 20. 4 x All data in memory for “regular” and “memory optimized”

Transaction CPU cycles (in millions) Speedup size in Memory-Opt #updates Regular 1 0. 91 0. 045 20. 2 x 10 1. 38 0. 059 23. 4 x 100 8. 17 0. 260 31. 4 x 1, 000 41. 90 1. 500 27. 9 x 10, 000 439. 00 14. 400 30. 5 x

Review and References

SQL Server RDBMS Column-Store Engine for DW Disk-optimized, row-store engine Memoryoptimized OLTP engine Blobs Native. Compiled Procedures Batch Query Operators Range + Column secondary indexes Readable Secondaries Transactions, Queries, Stored Procedures Always. On, Backup/Restore, Security, Admin, Deployment Available only in one engine Work across two engines with limitations Work across all engines but with limitations Nearly seamless across all engines Not comprehensive - only representative examples.

Apply to In-Memory BI (Power. Pivot, Power. View) In-Memory DW (columnstore) In-Memory OLTP

SIGMOD 2013 paper Kalen Delaney Whitepaper Video Slides Microsoft Virtual Academy talks MSDN Blog series § Other Presentations

Questions?

Breakout Sessions (in logical order)

SIGMOD 2013 paper Video Slides Microsoft Virtual Academy talks In-Memory OLTP Common Workloads and Migration Considerations In-Memory OLTP Internals Overview http: //msdn. microsoft. com/en-us/library/dn 511655(v=sql. 120). aspx In-Memory OLTP Blog Series Books Online: In-Memory OLTP

http: //www. try. SQLSever. com http: //www. powerbi. com http: //microsoft. com/bigdata

http: //channel 9. msdn. com/Events/Tech. Ed www. microsoft. com/learning http: //microsoft. com/technet http: //microsoft. com/msdn