Meet Kevin Liu Principal Lead Program Manager Kevin

Meet Kevin Liu • Principal Lead Program Manager • Kevin Liu has been with Microsoft and the SQL Server engine team for 7 years, working on key projects like Always. On and has been leading program management for the In-Memory OLTP project since its transition into the product team from incubation. • Prior to Microsoft, Kevin worked in enterprise software consulting (Accenture and etc) and holds a Ph. D on computational neural networks.

Kevin Liu| Principal Lead Program Manager Kevin. Liu@Microsoft. com

SQL Server 2014 Investments Enhanced High Availability 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 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 Other investments Better together with Windows Server • • 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. • Deployment wizard to migrate database 4

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

Why In-memory OLTP (Hekaton) ØHigh performance, ØMemory-optimized OLTP engine, ØIntegrated into SQL Server and ØArchitected for modern hardware trends 6

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 1000 10 1 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2004 2005 2007 2008 2009 2011 US$/GB 100000

Drivers Hekaton Tech Pillars Customer Benefits In-memory OLTP Architecture Pillars 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 via C code generator and VC • Invoking a procedure is just a DLL entry-point • Aggressive optimizations @ compile-time • Multi-version optimistic concurrency control with full ACID support • Core engine uses lock-free algorithms • No lock manager, latches or spinlocks • Optimized for in-memory data • Indexes (hash and range) exist only in memory • No buffer pool, B-trees • Stream-based storage • Same manageability, administration & development experience • Integrated queries & transactions • Integrated HA and backup/restore Business Hardware trends Steadily declining memory price, NVRAM Stalling CPU clock rate TCO Many-core processors 8

Demo 1 9

In-memory OLTP Integration and Application Migration Client App TDS Handler and Session Management Natively Compiled SPs and Schema In-Memory OLTP Compiler Parser, Catalog, Algebrizer, Optimizer Non-durable Table Query Interop In-memory OLTP Engine: Memory_optimized Tables & Indexes Checkpoint & Recovery Memory-optimized Table Filegroup Key Proc/Plan cache for ad-hoc TSQL and SPs Existing SQL Component Interpreter for TSQL, query plans, expressions Access Methods In-Memory OLTP Component T 1 T 2 T 3 T 4 Tables T 1 T 2 T 3 T 4 Indexes Buffer Pool for Tables & Indexes SQL Server. exe T 1 T 2 T 3 T 4 Transaction Log Data Filegroup 10 Generated. dll

Performance Gains Client App TDS Handler and Session Management No improvements in communication stack, parameter passing, result set generation 10 -30 x more efficient Reduced log bandwidth & contention. Log latency remains Checkpoints are background sequential IO Hekaton Compiler Natively Compiled SPs and Schema In-Memory OLTP Engine for Memory_optimized Tables & Indexes Parser, Catalog, Algebrizer, Optimizer Key Proc/Plan cache for ad-hoc T-SQL and SPs Interpreter for TSQL, query plans, expressions Access Methods Query Interop Buffer Pool for Tables & Indexes SQL Server. exe Memory-optimized Table Filegroup Transaction Log Data Filegroup Existing SQL Component Hekaton Component Generated. dll

SQL Server row-store and column-store scenarios • Row-store for OLTP: mainly for operational transaction with minimum reporting and shorter period of time • Column-store for DW: mainly for reporting of transaction history over a longer period of time Considerations Data size and currency Read Point select and ad hoc patterns query for operational report Large scan set with aggregates Star schema and related DW type of complex joins Write Heavy updates and patterns deletes IM Row store IM Updatable Columnstore Designed to address bottlenecks in hot data Designed for cold and archival data (>256 G) (For V 1, < 256 GB) Ideal – key design points for non-blocking Not ideal – minimum scan set is 1 M row + delta row store high performance data access Not ideal Ideal – key design points for contention free Functional but not optimized – change happens to on-disk row data operations store and gets merged into column store in batches Heavy ETL and data Ideal – key design points ingestion Relational cache scenario Ideal (with NDT) Functional but not optimized – same as above Not ideal

Demo 2 13

Join the MVA Community! • Microsoft Virtual Academy – Free online learning tailored for IT Pros and Developers – Over 1 M registered users – Up-to-date, relevant training on variety of Microsoft products • “Earn while you learn!” – Get 50 MVA Points for this event! – Visit http: //aka. ms/MVA-Voucher – Enter this code: Perf. SQL (expires 1/3/2014)

© 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics 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.