SQL Server 2014 Mission Critical Performance with SQL Server 2014 Jump Start

1. SQL Server 2014 Mission Critical Performance with SQL Server 2014 Jump Start

2. 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 AlwaysOn 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.

3. SQL Server 2014 In-Memory OLTP Overview Kevin Liu| Principal Lead Program Manager Kevin.Liu@Microsoft.com

4. SQL Server 2014 Investments Enhanced High Availability New Hybrid Scenarios In-Memory Technologies Other investments • 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 • 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 • Always On Enhancements • Increased availability and improved manageability of active secondaries • Online Database Operations • Increased availability for index/partition maintenance • 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. 4

5. In-memory Technologies In-Memory Technologies Applicable to Transactional workloads: Concurrent data entry, processing and retrieval • 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 Decision support workloads: Large scans and aggregates Applicable to Disk-based transactional workloads: Large working (data)set

6. Why In-memory OLTP (Hekaton) Market need for ever higher throughput and predictable lower latency OLTP at a lower cost HW trend demands 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 trends

7. Hardware trends Moore’s Law on total CPU processing power holds but in parallel processing… CPU clock rate stalled… Decreasing RAM cost

8. In-memory OLTP Architecture Pillars Customer Benefits High performance data operations Efficient, business-logic processing Frictionless scale-up Hybrid engine and integrated experience Hekaton Tech Pillars Main-Memory Optimized T-SQL Compiled to Machine Code High Concurrency SQL Server Integration • Multi-version optimistic concurrency control with full ACID support • Core engine uses lock-free algorithms • No lock manager, latches or spinlocks • 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 • Same manageability, administration & development experience • Integrated queries & transactions • Integrated HA and backup/restore • Optimized for in-memory data • Indexes (hash and range) exist only in memory • No buffer pool, B-trees • Stream-based storage Business Hardware trends Drivers Steadily declining memory price, NVRAM Stalling CPU clock rate TCO Many-core processors

9. Demo 1

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

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

12. 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

13. Demo 2

14. Join the MVA Community! • Microsoft Virtual Academy • Free online learning tailored for IT Pros and Developers • Over 1M 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: PerfSQL(expires 1/3/2014)