Columnstore Indexes in SQL Server 2014 Flipping the

Columnstore Indexes in SQL Server 2014 Flipping the DW /faster Bit: A Fast-Paced Introduction to an Exciting Feature Jimmy May, MCM SQL Server Solutions Architect jimmy. may@sandisk. com @aspiringgeek

Bio Jimmy May, MCM • San. Disk SQL Server Solutions Architect Formerly: • Sr. Program Manager, SQL CAT • SQL Server Customer Advisory Team • MSIT Principal Architect: Database Microsoft Certified Master: SQL Server (2009) MS IT Gold Star Recipient (2008) Microsoft Oracle Center of Excellence (2008) SQL Server MVP Nominee (2006) Indiana Windows User Group www. iwug. net • Founder & Board of Directors Indianapolis Professional Association for SQL Server www. indypass. org • Founder & Member of Executive Committee SQL Server Pros: Founder & Visionary-in-Chief SQL Innovator Award Recipient (2006) • Contest sponsored in part by Microsoft Formerly Chief Database Architect for high-throughput OLTP VLDB at Exact. Target (recent IPO) Senior Database Administrator for Open. Globe/Escient www. twitter. com/aspiringgeek jimmy. may@sandisk. com http: //sqlblog. com/blogs/jimmy_may jimmymay@outlook. com

Data Propulsion Laboratory — DPL

C: >sqlservr. exe /faster 5

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes 1. 2. 3. 4. 5. 6. Overview Architecture SQL Server 2012 vs. 2014 Implementation Scenarios Best Practices 4. More Info 6

Session Objectives And Takeaway Session Objectives 1. Understand that columnstore indexes can increase SQL Server DW query performance by one, two, or three orders of magnitude. 2. Understand the advantages & limitations of columnstore indexes in SQL Server 2012 & the significant improvements available in SQL Server 2014. Key Takeaway Understand the circumstances under which columnstore indexes may be a compelling reason to upgrade to SQL Server 2014 and start experimenting today. 7

Acknowledgements q q q Shahry Hashemi, Wingman, Capital. One Thomas Kejser, Fusion-io CTO (former SQL CAT PM) q Mike Ruthruff, Bungie Studios (former SQL CAT PM) q q Justin Lane, Principal PM q Susan Price, SQL Server PM (former) q Eric Hansen, SQL Server PM q Gus Apostol, SQL Server PM q Gavin Payne, MCA, COEO Consultant Steve Fisher, MSIT Senior Service Engineer Le. Roy Tuttle, Sr. SDE Chris Adkin Mahesh Jambunathan, SDE Hrair Kerametlian, Principal PM Emanuel Rivera Aleman (EMAN), Sr. SE Bill Kan, Sr. SE Igor Stanko, Principal PM Srikumar Rangarajan, Principal Dev Lead Stanislav Novoseletskiy, Principal Platform Specialist 8

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes 1. 2. 3. 4. 5. 6. Overview Architecture SQL Server 2012 vs. 2014 Implementation Scenarios Best Practices 4. More Info 13

Demo: Columnstore vs. Conventional Index Performance 14

What is a SQL Columnstore Index? q Codename Apollo q Part of SQL Server in-memory family of technologies Common code path with Verti. Paq § Power. Pivot, Power. View, SSAS § q Contrasted with traditional row stores in which data is physically stored row-by-row q Columnstores values for all rows for a given column 15

Row Store vs. Columnstore: A Metaphor If only we were live & in-person. . . SQL Server 2014 Launch Event on May 31, 2014 #SQLSaturday 306 Images starring #SQLWingman @dsfnet & @aspiringgeek courtesy Tom Roush @GEEQL Tweet: https: //twitter. com/GEEQL/status/472861392039473153 16

Columnstore High-Level Characteristics q Highly compressed q Aggressive readahead q In-memory/disk hybrid structures q Processes data in units called "batches“ q Vector-based query execution q Query Optimizer automatically considers columnstore indexes during compilation q Numerous deep engine modifications § I/O, Memory, & Caching 17

Why Use Columnstore Indexes? q Designed to optimize access to large DWs (vs. OLTP) § Star schema, large fact tables (esp. integer keys), aggregations, scans, reporting q Faster, interactive query response time q Transparent to the application § Most things “just work” Backup and restore § Mirroring, log shipping § SSMS, etc. § There are some gotchas (stay tuned…) § 18

Why Use Columnstore? (cont. ) q Reduced physical DB design effort § Fewer (or no!) conventional indexes § Reduced need for summary aggregates and indexed views § May eliminate need for OLAP cubes q Lower TCO § Yes, I had to say this! 19

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes Overview 2. Architecture 3. SQL Server 2012 vs. 2014 4. Implementation 5. Scenarios 6. Best Practices 1. 4. More Info 20

Columnstore Index Architecture Segment C 1 C 2 C 3 C 4 C 5 q Row Group § 1 million logically contiguous rows C 6 q Column Segment § Row Group § § Segment contains values from 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 Segment is unit of transfer between disk and memory 23

Columnstore Index Example Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 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 24

1. Horizontally Partition (Row Groups) Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 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 Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 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 25

2. Vertically Partition via Columns (Segments) Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 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 Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 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 26

3. Compress Each Segment* Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 20101107 106 01 1 6 30. 00 04 2 1 17. 00 20101108 103 109 03 2 05 4 5 02 20. 00 25. 00 Order. Date. Key Product. Key Store. Key Region. Key Quantity Sales. Amount 20101108 102 02 1 1 14. 00 03 2 5 25. 00 20101109 106 109 01 103 04 4 10. 00 25. 00 17. 00 Some segments will compress more than others *Encoding and reordering not shown 27

Columnstore in SQL 2014 q Fully Read/Write § More transparency to the app than ever § No need for “trickle-loading” or other workarounds §. . . Yet Partition switching & BULK INSERT remain best practices q Data type support expanded: § All data types except: (n)varchar(max), varbinary(max), XML, Spatial, CLR § Basically, SQL 14 columnstore is compatible with all non-blob datatypes 33

Columnstore in SQL Server 2014 (cont. ) q New: Clustered Columnstore § Dependency on conventional b-tree structures has been removed § Potential for significant disk space savings if workload is satisfied without conventional indexes q Note: Non-clustered columnstore is still supported & is still a read-only structure § Required if: § Constraints are required § Workload requires b-tree non-clustered indexes 34

Columnstore in SQL Server 2014 (cont. ) q “Batch mode”: § Query Processor vector-based (L 1 cache resident) operations expanded, improved § New support for: • All joins (including OUTER, HASH, SEMI (NOT IN, IN) • UNION ALL • Scalar aggregates • “Mixed mode” plans q Improvements to bitmap, spill support, etc. q Hash join enhancements 35

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes Overview 2. Architecture 3. SQL Server 2012 vs. 2014 4. Implementation 5. Scenarios 6. Best Practices 1. 4. More Info 40

Scenarios q Customer: Motricity: Case Study q MSIT SONAR App: Aggregations § booyah! q Customer: Dev. Con Security: Production App § double-booyah! q Windows Telemetry—Watson § triple-booyah! q MSIT Problem Management App § booyahnth q Microsoft Fast Track 41

SQL CAT Customer: Motricity q Sybase customer q Demographic data & app required columnstore capabilities q Proved out technology on Denali bits q Numerous learnings discovered & incorporated into SQL Server 2012 RTM q Production implementation leveraged partitioning & “trickle loading” (& Always. On Availability Groups) q Case Study: http: //www. microsoft. com/casestudies/Microsoft-SQL-Server -2012 -Enterprise/Motricity/Mobile-Advertiser-Makes-Gainswith-Easy-Migration-of-Sybase-Database-to. Microsoft/710000000170 q Customers: @armpitslinky, @SQLJack. Burton, & the cuddliest customer ever in the SQL CAT Lab: @dsfnet 42

MSIT SONAR App Po. C Logical Reads CPU Durn 160, 323 20, 360 9, 786 9, 053, 423 549, 608 193, 903 x 56 x 27 x 20 (8 K pages) Columnstore Conventional Table & Indexes Δ (ms) Conventional vs. Columnstore Metrics 10, 000 Metrics (Δ) 60 x[VALUE] 9, 000 50 8, 000 7, 000 40 6, 000 5, 000 30 x[VALUE] 4, 000 x[VALUE] 20 3, 000 2, 000 10 1, 000 - Logical Reads (data pages) CPU (ms) Durn (ms) Copyright © 2015 San. Disk. All rights reserved.

Customer: Dev. Con Security: Production App q Revenue History Fact Table q Table stats 22 M rows § 33 GB data § 137 columns § q Indexing strategery challenging, especially for ad hoc queries 44

Customer: Dev. Con Security: Production App: SSRS q Scenario 1: SSRS § Before: SSAS cube processing + batch process ran MDX to dump results into a separate table § “…hassle to maintain” q Before: Queries against conventional table structures: § 10 - 12 seconds q After: Queries against Columnstore: § 1 second § One order of magnitude faster 45

Customer: Dev. Con Security: Production App: Ad Hoc Queries q Scenario 2: Ad Hoc Queries q Before: Queries took 5 – 7 minutes (300 – 420 seconds) q After: Columnstore: 1 – 2 seconds § Two orders of magnitude faster 46

Customer: Dev. Con Security: Production App: Outcomes q Unsolicited feedback: § “What we found was pretty awesome. ” § “It has been a game changer for us in terms of the flexibility we can offer people that would like to get to the data in different ways. ” — Nathan Allphin, BI Team Lead q Customer is deprecating this aspect of their SSAS infrastructure in favor of columnstore 47

Windows Telemetry—Watson WER Failure Event Hit Counting: Aggregate record of all occurrences of user mode failures on Windows machines worldwide q Data: 61 billion rows, 6440 partitions, partitioned by date (12+ years), keyed on Day, i. Bucket, LCID, OEM, OS Version q ETL: SSIS flat files into staging HEAP table, indexes built to match target, perform SWITCH (+300 M rows daily) q Formerly hours, now 5 -minute queries! (50 -100 x gain) § § Total hits by day, for all days Filtered rollup on 3 dimensions Acknowledgements: Le. Roy Tuttle, Sr. SDE Mahesh Jambunathan, SDE q Features/Tricks/Lessons: § String values & SQLCLR UDT column located in dimension tables § Really bad perf hit if Foreign Keys are not trusted (is_not_trusted = 1) § Clustered index order can make huge difference in columnstore efficiency by grouping repeating values § MAXDOP configuration tradeoff; columnstore queries want it, other queries suck too much memory if MAXDOP is set too high § Lack of query partition elimination diminishes columnstore gains 49

MSIT Problem Management App q Interactive app for which queries returned results in unpredictable time frame: “ 2 – 7 minutes” q Wide table: 151 columns all commonly subject to interrogation § § DB Dev troubleshooting time & expertise scarce Indexing strategery difficult/impossible q Table stats 3. 1 M rows § 15 GB data (barely fits in memory; lots of churn) § 1. 7 M data pages q Columnstore index: 478 pages (memory resident) § 50

MSIT Problem Management § How to index a table with these columns? Ticket. Folder. ID, Module. Name, Module. Sub. Type, Ticket. ID, Created. Date, Created. By. Alias, Created. By. Full. Name, Created. By. Location. Country, Created. By. Location. City, Created. By. Location. Building, Source, Contact. Type, Contact. Alias, First. Name, Last. Name, Email, Business. Customer, Contact. Location. Country, Contact. Location. City, Contact. Location. Building, Contact. Location. Alt. Country, Contact. Location. Alt. City, Contact. Location. Alt. Building, Asset. Type, Asset. Service, Service. Category, Asset. Name, Asset. Model 1, Asset. Description, Asset. Manufacturer, Asset. Make 1, State, Status, Summary, Impact, Urgency, Priority, Contact. Reason, Details, Environment. Specific, Scheduled. Date, Current. L 1, Current. L 2, Current. L 3, Current. Group. ID, Current. Group, Current. Sub. Group. ID, Current. Sub. Group, Current. Team. ID, Current. Team, Current. Individual, Assigned. To. Name, SLADeadline, Resolved. At. Date, Response. Deadline, Responded. At. Date, Affected. Service, Requested. Service, Resolution. Category, Close. Details, Final. Status, Failure. Impact, Service. Outage, CIOutage, KBArticle. Action, KBArticle. Title, KBArticle. ID, Resolved. By. Alias, Resolved. By. Full. Name, Resolved. By. Location. Country, Resolved. By. Location. City, Resolved. By. Location. Building, Resolved. By. Employee. Type, Resolved. By. Company, Major. Incident. Review. Date, Major. Incident. Review. Outcome, Addl. Contacts, On. Behalf. Of, Original. Priority, Original. Service. Category, Original. Asset. Type, Original. Asset. Service, Original. Asset. Name, Original. Asset. Model, Original. Asset. Description, Original. Asset. Manufacturer, Original. Contact. Reason, Original. L 1, Original. L 2, Original. L 3, Original. Group. ID, Original. Group, Original. Subgroup. ID, Original. Subgroup, Original. Team. ID, Original. Team, Original. Individual, Alt. Telephone, Alt. Location, Location. Type, Ticket. Location, Reactivation. Reason, Closed. Date, Email. Sent. To. Queue. Date, First. Non. Auto. Response. Act. Type, First. Escalation. L 1 to. L 2 Flag, First. Escalation. L 1 to. L 2 Date, L 2 Escalation. Group. ID, L 2 Escalation. Group, L 2 Escalation. Subgroup. ID, L 2 Escalation. Subgroup, L 2 Escalation. Team. ID, L 2 Escalation. Team, L 2 First. Non. Auto. Response. Date, L 2 First. Non. Auto. Response. Activity, First. Escalation. L 2 to. L 3 Flag, First. Escalation. L 2 to. L 3 Date, L 3 Escalation. Group. ID, L 3 Escalation. Group, L 3 Escalation. Subgroup. ID, L 3 Escalation. Subgroup, L 3 Escalation. Team. ID, L 3 Escalation. Team, L 3 First. Non. Auto. Response. Date, L 3 First. Non. Auto. Response. Activity, Third. Party. Pend. Status. Duration, Third. Party. Reference, Last. Updated. Date, Pending. Closure, Third. Party. Name, Third. Party. Other, Sub. Group. Transfer. Flag, Sub. Group. Response. Deadline, Sub. Group. Response. Date, Primary. Config. Folder. ID, Service. Config. Folder. ID, KBArticle. Details, Cluster. Nodes, Resolvable. By. Previous. Team, Resolve. Comments, Client. Pending. Status. Duration. Min, Parts. Pending. Status. Duration. Min,

Typical Query --Current. Group, Current. Sub. Group, Module. Name SELECT TOP 10 Current. Group, Current. Sub. Group, KBArticle. Title, COUNT(*) as Row. Cnt FROM dbo. ITSM 01 ISRM WHERE Current. Group = 'Ex. D Services' AND Created. Date > dateadd(dd, -30, getdate()) AND Current. Sub. Group = 'SAP' AND Status = 'Resolved' GROUP BY Current. Group, Current. Sub. Group, Module. Name ORDER BY COUNT(*) DESC --OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX); 52

Query Duration used to be Unpredictable But Columnstore renders consistent subsecond results Duration (minutes) 10: 00 8: 37 9: 00 8: 00 7: 00 6: 00 5: 00 4: 00 3: 00 2: 00 1: 00 0: 00 2: 33 2: 27 1: 06 1: 37 0: 35 0: 00 B-Tree Test B-Tree Test Columnstore #1 #2 #3 #4 #5 #6 53

y tle Ti le tic Ar KB or eg at am t. T e en lu tio n. C so Re rr Cu on y rit as Re ac t nt Co io Pr n tio l Em ai ip cr es t. D se As ce ur So e am e. N ul od M Conventional vs. CS Perf: Duration 250, 000 Duration (ms) 200, 000 150, 000 100, 000 Conventional CS 50, 000 0 54

y tle Ti le tic Ar KB or eg at n am t. T e en lu tio n. C so Re rr Cu y rit Re as o ct nt a Co io Pr l n tio ip cr es t. D se As 1, 000 Em ai ce ur So e am e. N ul od M Conventional vs. CS Perf: Logarithmic Duration (ms) 100, 000 1, 000 Conventional CS 100 10 55

y tle Ti le tic Ar KB or eg at m on t. T ea en n. C lu tio so Re rr Cu y rit as Re ct nt a Co io Pr l n tio ip cr es t. D se As 1, 000 Em ai ce ur So e e. N am ul od M Conventional vs. CS Perf: I/O (8 KB data pages) 100, 000 10, 000 Conventional 1, 000 CS 100 10 56

or y tle Ti le tic Ar KB eg at am n so t. T e en lu tio n. C so Re rr Cu y rit io ea t. R nt ac Co Pr n tio l Em ai 100, 000 ip cr es t. D se As ce ur So e am e. N ul od M Conventional vs. CS Perf: CPU (ms) 10, 000 1, 000 CPU (ms) Conventional 100 CPU (ms) CS 10 57

Row Store vs. Columnstore: I/O vs. Query Throughput I/O Throughput 3, 000 10, 000 9, 000 2, 500 8, 000 7, 000 2, 000 6, 000 5, 000 1, 500 4, 000 1, 000 3, 000 2, 000 500 1, 000 0 #2014 -003 #2014 -002 RS Scan Rate Physical (MB/sec) CS Scan Rate Physical (MB/sec) #2014 -009 #2014 -032 0 #2014 -003 #2014 -002 RS Measured Thruput (Queries/Hr/TB) #2014 -009 #2014 -032 CS Measured Thruput (Queries/Hr/TB) Copyright © 2015 San. Disk. All rights reserved.

Lenovo 55 TB Data Warehouse Fast Track Certification Breakthrough Performance - 11. 3 GB/s throughput - 2548 queries / Hr / TB - 98% average CPU utilization NEW! San. Disk-based 3200 GB Lenovo io 3 Flash Adapters System x 3850 X 6 Server Copyright © 2015 San. Disk. All rights reserved.

Fast Track References q Microsoft Fast Track: Learn more http: //www. microsoft. com/en-us/server-cloud/products/sql-server q Fast Track Data Warehouse Reference Guide for SQL Server 2012 https: //msdn. microsoft. com/en-us/library/hh 918452. aspx q Certified DWFTRA solutions for SQL Server (scroll down slightly) http: //www. microsoft. com/en-us/server-cloud/data-warehouse-fast-track. aspx Copyright © 2015 San. Disk. All rights reserved.

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes Overview 2. Architecture 3. SQL Server 2012 vs. 2014 4. Implementation 5. Scenarios 6. Best Practices 1. 4. More Info 62

Syntax Similar to Existing DDL CREATE NONCLUSTERED COLUMNSTORE INDEX ix_cs_My. DWTable ON dbo. My. DWTable (col 1 , col 2 , . . . , coln); CREATE CLUSTERED COLUMNSTORE INDEX ix_cs_My. DWTable ON dbo. My. DWTable --WITH (DROP_EXISTING = ON) ; --no column list! DROP INDEX dbo. My. DWTable. ix_cs_My. Table; ALTER INDEX dbo. My. DWTable. ix_cs_My. Table DISABLE; ALTER INDEX dbo. My. DWTable. ix_cs_My. Table ENABLE; 63

Hints to Force / Prevent Usage q Force the Optimizer to use: …FROM dbo. My. DWTable WITH (INDEX (ix_cs_My. Table))… q Prevent the Optimizer from using: SELECT… FROM dbo. My. DWTable WHERE… GROUP BY… ORDER BY… OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX); 64

Demo: Columnstore Index DDL 65

Building Columnstore Indexes Fast! q Memory resource intensive § Memory requirement related to number of columns, data, DOP q Unit of parallelism is the segment § Lots of segments, lots of potential parallelism q Low memory throttles parallelism § Increase the max server memory option § Set REQUEST_MAX_MEMORY_GRANT_PERCENT to 50 § Add physical memory to the system § Adaptive index build process: adjust DOP and segment size 66

Columnstore ♥ Parallelism q In general, SQL Server will leverage all available CPUs for creating indexes or issuing readahead for satisfying queries § True for columnstore or conventional indexes 67

Demo: Leveraging Resource Governor 68

Batch Size Case Study q Problem: Query Perf Degrading § Formerly minutes, worsening to 67 minutes q Scenario 1000 row inserts § 4, 056 "OPEN" Row Groups § Join to Hekaton table § q Remedy 1. 2. REBUILD Columnstore Index Leverage 100, 000 row inserts q Outcome 522 Row Groups § 23 sec (x 174!) § No loss of concurrency § q Recommendation § Leverage partitioning, result in fewer, larger Row Groups 73

Demo: Table Partitioning 74

Agenda 1. Introduction & Preamble 2. SQL Server 2012 & 2014: New! Improved! Features 3. Columnstore Indexes 1. 2. 3. 4. 5. 6. Overview Architecture SQL Server 2012 vs. 2014 Implementation Scenarios Best Practices 4. More Info 80

Columnstore Indexes: More Info The following is a must-read: SQL Server Columnstore Index FAQ Eric Hanson, Susan Price, etc. http: //social. technet. microsoft. com/wiki/contents/articles/3540. sql-server-columnstoreindex-faq-en-us. aspx Microsoft Internal: SQL 14 What's new with Always. On and Column Store Indexes Other references: My blog http: //blogs. msdn. com/jimmymay Niko Neugebauer’s blog http: //www. nikoport. com Columnstore Indexes http: //msdn. microsoft. com/en-us/library/gg 492088(SQL. 110). aspx SQL Server Columnstore Performance Tuning http: //social. technet. microsoft. com/wiki/contents/articles/4995. sql-server-columnstore-performance-tuning. aspx Trickle Loading with Columnstore Indexes http: //social. technet. microsoft. com/wiki/contents/articles/trickle-loading-with-columnstore-indexes. aspx How do Column Stores Work? Thomas Kejser http: //blog. kejser. org/2012/07/04/how-do-column-stores-work This is Columnstore – Part 1 Gavin Payne, MCM http: //gavinpayneuk. com/2012/07/22/this-is-columnstore-part-1 81

Thank You!