Mission-critical performance

1. SQL Server 2014 and the Data PlatformKarl-Heinz SütterlinTechnology Solution Professional DataPlatformKarls@microsoft.comMicrosoft Switzerland

2. Mission-critical performance In-memory built-in Average 10x faster for new and existing SQL Server apps Secure and scalable Most secure with enterprise scale using Windows Server High availability The 9’s you need, with AlwaysOn Mission-critical support Live support designed for mission-critical solutions

3. In-memory built-in Key Features Built-in On average 10x faster, without need to rewrite entire app Uses full Microsoft SQL Server capabilities New In-Memory OLTP Enhanced In-Memory Columnstore for DW In-Memory BI with PowerPivot Buffer Pool Extension to SSDs and Enhanced Query Processing Flexible Selects only highly-utilized tables to be in-memory Optimizes in-memory to fit existing hardware Before 16x fasterwith in-memory Bwin Game Application Bwin Game Application 15,000 requests/sec 250,000 requests/sec Spans all workloads In-memory performance across OLTP, data warehouses, and BI All in a single SKU OLTP SQL Server 2014 with in-memory

4. Our strategy for in-memory computing • Deliver an end-to-end solution with frictionless scale and breadth of delivery model • Built into your existing platform and seamlessly spans memory-only and hybrid environments • Business acceleration via a high-velocity virtuous cycle from raw data to insights to business transformation

5. Microsoft in-memory technologies Analytics Streaming Data OLTP • In-Memory Analytics in PowerPivot for Excel • In-Memory Analytics in SQL Server Analysis Services • In-Memory Columnstore Index in SQL Server 2012, Fast Track Data Warehouse, and Parallel Data Warehouse • StreamInsight • In-Memory OLTP • In-Memory Columnstore Index (in SQL Server 2014) 2014 2012 2014 2012 2014

6. ‘HEKATON’In-Memory OLTP Engine

7. In-Memory for OLTP New high-performance, memory-optimized OLTP engine integrated into SQL Server and designed for modern hardware trends Integrated into SQL Server relational database Full ACID support Memory-optimized indexes (no B-trees or buffer pools) Non-blocking multiversion optimistic concurrency control (no locks or latches) T-SQL compiled to native code Customer benefits • Low latency • Up to 10x improvement in performance • 2–5x improvement in scalability • Takes advantage of investments in SQL Server

8. Suitable application characteristics Application is suited for in-memory processing All performance-critical data already fits in-memory Transaction locking or physical latching causing stalls and blocking Application is “OLTP-like” Relatively short-lived transactions High degree of concurrent transactions from many connections Examples: stock trading, travel reservations, and order processing Application porting simplified under certain conditions Stored procedures used Performance problems isolated to subset of tables and stored procedures

9. In-Memory OLTP architecture Benefits High-performance data operations Frictionless scale-up Efficient, business-logic processing Hybrid engine and integrated experience In-Memory OLTP Tech Pillars Main-memory optimized High concurrency T-SQL compiled to machine code SQL Server integration • Multiversion 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 Visual C compiler • Invoking a procedure is just a DLL entry-point • Aggressive optimizations at compile time • Same manageability, administration, and development experience • Integrated queries and transactions • Integrated HA and backup/restore • Optimized for in-memory data • Indexes (hash and range) exist only in memory • No buffer pool • Stream-based storage for durability Business Hardware trends Drivers Steadily declining memory price, NVRAM Stalling CPU clock rate TCO Many-core processors

10. Design considerations for memory-optimized tables Table constructs Fixed schema; no ALTER TABLE; must drop/recreate/reload No LOB data types; row size limited to 8,060 No constraints support (primary key only) No identity or calculated columns, or CLR Data and table size considerations Size of tables = (row size * # of rows) Size of hash index = (bucket_count * 8 bytes) Max size SCHEMA_AND_DATA = 512 GB IO for durability SCHEMA_ONLY vs. SCHEMA_AND_DATA Memory-optimized filegroup Data and delta files Transaction log Database recovery Benefits High performance data operations Main-memory optimized In-Memory OLTP Tech Pillars • Optimized for in-memory data • Indexes (hash and range) exist only in memory • No buffer pool • Stream-based storage for durability Drivers Hardware trends Steadily declining memory price, NVRAM

11. High concurrency design considerations Impact of no locks or latches Write-write conflict: design application for condition with try.catch Applications dependent on locking; may not be a good candidate Multiple versions of records Increases the memory needed by memory-optimized tables Garbage Collection used to reclaim old versions Transaction scope Support for Isolation Levels: Snapshot, Repeatable Read, Serializable Commit time validation; again must retry logic to deal with failure Frictionless scale-up Benefits In-Memory OLTP Tech Pillars High concurrency • Multiversion optimistic concurrency control with full ACID support • Core engine uses lock-free algorithms • No lock manager, latches, or spinlocks Drivers Hardware trends Many-core processors

12. In-Memory OLTP Concurrency Control Multiversion data store Snapshot-based transaction isolation No TempDB Multiversion No locks, no latches, and minimal context switches No blocking Conflict detection to ensure isolation No deadlocks Optimistic

13. Supported isolation levels SNAPSHOT Reads are consistent as of start of the transaction Writes are always consistent REPEATABLE READ Read operations yield same row versions if repeated at commit time SERIALIZABLE Transaction is executed as if there are no concurrent transactions; all actions happen at a single serialization point (commit time)

14. Example: write conflict First writer wins

15. Design considerations for native compiled stored procedures Benefits Efficient business-logic processing In-Memory OLTP Tech Pillars T-SQL compiled to machine code • T-SQL compiled to machine code via C code generator and Visual C compiler • Invoking a procedure is just a DLL entry-point • Aggressive optimizations at compile-time Drivers Hardware trends Stalling CPU clock rate

16. Natively compiled stored procedures Access only memory-optimized tables Max performance Limited T-SQL surface area When to use OLTP-style operations Optimize performance-critical business logic Interpreted T-SQL access Access both memory- and disk-based tables Less performance Virtually full T-SQL surface area When to use Ad-hoc queries Reporting-style queries Speeding up app migration Accessing memory-optimized tables

17. 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 In-Memory OLTP compiler In-Memory OLTP component Natively compiled SPs and schema Interpreter for T-SQL, query plans, expressions 10–30x more efficient Generated .dll Access methods Query Interop In-Memory OLTP engine for memory-optimized tables and indexes Buffer pool for tables and indexes Reduced log bandwidth and contention; log latency remains Transaction log Data filegroup Memory-optimized table filegroup Checkpoints are background sequential IO

18. In-Memory OLTP Benefits SQL Server engine TDS handler and session management • Low latency • Up to 10 times the improvement in performance • 2–5 times the improvement in scalability • Takes advantage of investments in Microsoft SQL Server Parser, catalog, optimizer In-Memory OLTP compiler Poc/plan cache for ad-hoc, T-SQL; interpreter, plans Native compiled SPs and schema In-Memory OLTP Engine: memory-optimized tables and indexes Buffer pool for tables and indexes How it works • New high-performance, memory-optimized OLTP engine integrated into SQL Server and architected for modern hardware trends • Integrated into SQL Server relational database • Full ACID support • Memory-optimized (no B-tree indexes or buffer pools) • Non blocking multiversion optimistic concurrency control (no locks or latches) • T-SQL compiled to native code Memory-optimized table file group Transaction log Data file group

19. SQL Server integration design drilldown Benefits Hybrid engine and integrated experience SQL Server integration In-Memory OLTP Tech Pillars • Same manageability, administration, and development experience • Integrated queries and transactions • Integrated HA and backup/restore Drivers Business TCO

20. Integrated experience Backup and restore Full and log backup and restore is supported; piecemeal restore is supported Failover clustering Failover time depends on size of durable memory-optimized tables AlwaysOn Secondary has memory-optimized tables in memory Failover time is not dependent on size of durable memory-optimized tables DMVs, catalog views, performance monitor counters, XEvents Monitoring memory, garbage collection activity, and transaction details SSMS Creating, managing, and monitoring tables; databases and server

21. Memory considerations Symptom Diagnosis Solution Scenario Free up memory Add memory Identify and stop long running transactions Transactions start failing Read error log Identify via DMVs or SSMS whether In-Memory OLTP is using most memory Inserting more rows than rows that can fit in memory Recovering a database that does not fit in memory Database does not come online Memory pressure from In-Memory OLTP workload on other workloads Operations in other workloads start failing

22. In-Memory OLTP storage considerations Capacity needed is 2–3 times the size of durable memory-optimized tables Use sequential bandwidth sufficient to meet RTO Spinning media Data Per transaction log consumption is less than disk-based tables Latency is important SSDs Log

23. In-Memory OLTP summary • What’s being delivered • High-performance, memory-optimized OLTP engine integrated into SQL Server and architected for modern hardware trends • Main benefits • Optimized for in-memory data up to 20–30 times throughput • Indexes (hash and range) exist only in memory; no buffer pool, B-trees • T-SQL compiled to machine code via C code generator and Visual C compiler • Core engine uses lock-free algorithms; no lock manager, latches, or spinlocks • Multiversion optimistic concurrency control with full ACID support • On-ramp existing applications • Integrated experience with same manageability, administration, and development experience

24. ColumnStoreIn-Memory in the Datawarehouse

25. In-memory in the data warehouse Data stored row-wise: heaps, B-trees, key-value “By using SQL Server 2012 xVelocity, we were able to extract about 100 million records in 2 or 3 seconds versus the 30 minutes required previously. “ Existing Tables (Partitions) • Columnstore index • Both memory and disk • Built-in to core RDBMS engine Customer Benefits: • 10-100x faster • Reduced design effort • Work on customers’ existing hardware • Easy upgrade; easy deployment C4 C5 C6 C2 C3 C1 - Atsuo Nakajima Asst Director, Bank of Nagoya Columnstore index representation Needed columns

26. Relational, dimensional, and map reduce Traditional storage modelsData stored row-wise: heaps, B-trees, key-value …

27. Each page stores data from a single column Highly compressed More data fits in memory Each column can be accessed independently Fetch only columns needed Can dramatically decrease I/O Columnstore storage modelData stored column-wise: columnstore C1 C4 C5 C6 C3 C2

28. A segment contains values for one column for a set of rows Segments for the same set of rows comprise a row group Segments are compressed Each segment stored in a separate LOB A segment acts as a unit of transfer between disk and memory Columnstore index structureRow groups and segments Segments C1 C4 C5 C6 C3 C2 Rowgroup

29. Columnstore indexProcessing an example

30. Horizontally PartitionRow groups

31. Vertical PartitionSegments

32. *Encoding and reordering not shown Compress each segment*Some compress more than others

33. Fetch only needed columnsSegment elimination

34. Fetch only needed segmentsSegment elimination

35. Biggest advancement in query processing in years Data moves in batch through query plan operators Minimizes instructions per row Takes advantage of cache structures Highly-efficient algorithms Better parallelism Batch modeImproving CPU utilization

36. Batch mode processingQP vector operators • Process ~1000 rows at a time • Batch stored in vector form • Optimized to fit in cache • Vector operators implemented • Filter, hash join, and hash aggregation • Greatly reduced CPU time (7–40x) Batch object Column vectors Bitmap of qualifying rows

37. Columnstores: clustered and updatableThe next generation • Fast execution for data warehouse queries • Speedups of 10x and more • No need for separate base table • Saves space • Data can be inserted, updated, or deleted • Simpler management • Eliminate need for other indexes • Saves space and simpler management • More data types supported • Removes many limitations from non-clustered columnstores in SQL Server 2012

38. Comparing space savings101-million row table and index space

39. CREATE CLUSTERED COLUMNSTORE Organizes and compresses data into columnstore BULK INSERT Creates new columnstore row groups INSERT Rows are placed in the row store (heap) When row store is big enough, a new columnstore row group is created DELETE Rows are marked in the deleted bitmap UPDATE Delete plus insert Most data is in columnstore format Structure of a columnstore indexHow it works Partition Deletedbitmap Columnstore Row store

40. Adds an additional layer of compression on top of the inherent compression used by columnstore Shrink on-disk database sizes by up to 27 percent Compression applies per partition and can be set either during index creation or during rebuild Archival compressionWhat’s new?

41. Bulk insert Creates row groups of one million rows; last row group is probably not full But if less than 100,000 rows, data will be left in a row store Insert/update Collects rows in a row store Tuple Mover When the row store reaches one million rows, convert to a columnstore row group Runs every five minutes by default Started explicitly by ALTER INDEX <name> ON <table> REORGANIZE Insert and updating dataLoad sizes

42. Columnstore enhancements summary • What’s being delivered • Clustered and updateable columnstore index • Columnstore archive option for data compression • Global batch aggregation • Main benefits • Real-time super fast data warehouse engine • Ability to continue queries while updating without the need to drop and recreate index or partition switching • Huge disk space saving due to compression • Ability to compress data 5–15x using archival per-partition compression • Better performance and more efficient (less memory) batch query processing using batch mode rather than row mode

43. AlwaysOn in SQL Server 2014 • What’s being delivered • Increase number of secondariesfrom four to eight • Increase availability of readable secondaries • Support for Windows Server 2012 CSV • Enhanced diagnostics • Main benefits • Further scale out read workloads across (possibly geo-distributed) replicas • Use readable secondaries despite network failures (important in geo-distributed environments) • Improve SAN storage utilization • Avoid drive letter limitation (max 24 drives) via CSV paths • Increase resiliency of storage failover • Ease troubleshooting

44. HA/DR features before Failover Clustering Database Mirroring Log Shipping Virtual Networkname (Transparent) Fast Failover Multiple Secondaries Multidatabase Failover NoShared Storage needed NoShared Storage needed Shared Storage needed • Application has to be “mirroring-aware” (SNAC or connectstring modification) • Needs good monitoring • No active secondaries available • No virtual network name to connect • No automatic failover DR only with Storage Replication or additional Logshipping/ Database Mirroring Only 1 active secondary with database snapshots on the mirror • Secondaries offline while applying transactionlog backups

45. AlwaysOn Availability Group Flexible Integrated Efficient • Multi-database failover • Multiple secondaries • Total of 8 secondaries • 2 synchronous secondaries • 1 automatic failover pair • Synchronous and asynchronous data movement • Built in compression and encryption • Flexible failover policy • Automatic Page Repair • Application failover using virtual name • Configuration Wizard • Dashboard • System Center Integration • Rich diagnostic infrastructure • File-stream replication • Replication publisher failover • Active Secondary • Readable Secondary • Backup from Secondary • Automation using power-shell

46. The Availability Group (AG) DB DB DB • «Container» • 1 or more Databases • Listener (aka virtual networkname) • 1 or more IP Addresses (DHCP or fix, aka virtual IP) • Replica Definition • Primary/Secondaries • Automatic Failover Partner • Sync/Async Secondaries • Readonly Routinglists AG AG-VNN AG-IP

47. Failover using Virtual Name and ActiveSecondary • Availability Group Virtual Name allow applications to failover seamlessly on availability group failover • Readable secondary allows offloading read queries to secondary • Automatic redirect with Routing Lists or manual connect to Secondary Reports WSFC DENALIWINCLU1 SalesStoreAG1 CALGARY TORONTO VANCOUVER DB2 DB2 DB2 DB1 DB1 SalesStore1 (VNN) DB1 AG-IP Primary Secondary Primary Secondary Secondary • Connect to new primary/new secondary once failover is complete and the listener is online • Application retry during failover • -server SalesStore1 • -server SalesStore1; ApplicationIntent=ReadOnly

48. Hybrid cloud solutions Reduce CAPEX and OPEX with cloud DR Simplified cloud backup Manual or automatic At an instance level with point-in-time restore Measures database usage patterns to set backup frequency Run backups Windows Azure Run BI reports Secondary replica Cloud disaster recovery Fast disaster recovery (low RTO) Easy to deploy and manage VPN tunnel (Windows Azure Virtual Network) Asynchronous commit On-premises network Extend on-premises apps Cloud bursting Greater global reach Better isolation of internal assets Domain controller Synchronous commit Primary replica Secondary replica

49. Why do we need cloud DR for SQL Server? Event can cause on-premises SQL Server to become unavailable • Temporarily (for example, gateway failure) • Permanently (for example, flooding, fire….) Disaster recovery site is expensive • Site rent + maintenance • Hardware • Ops

50. Typical configuration Windows Azure On-Premises