Sofia Event Center 21-22 November 2013

1. Sofia Event Center 21-22 November 2013 DI6 HandsON: Mission-critical performance and manageability Margarita Naumova SQL Master Academy

2. HandsON Scenarios HandsOn scenario InMemory OLTP – achieve a great performance InMemory DW - CSI and NonClsustered CSI – what is the difference in 2014 Resource Governor to control IO Resource Governor and InMemory OLTP working together to control memory

3. Administrator pass@word1

4. In-Memory OLTP

5. Memory-optimized Table: Row Format Key Points Begin/End timestamp determines row’s validity No data or index page; just rows Row size limited to 8060 bytes to allow data to be moved to disk-based table Not every SQL table schema is supported Row header Payload (table columns) 8 bytes * (IdxLinkCount – 1) End Ts StmtId IdxLinkCount Begin Ts 2 bytes 8 bytes 8 bytes 4 bytes

6. Key lookup: B-tree vs. Memory-optimized Table Non clustered index Hash index on Name R1 R2 R3 Matching index record

7. Memory Optimized Tables and Indexes Timestamps Chain ptrs Name City Hash index on City Hash index on Name f(Jane) f(Prague) f(Susan) f(Bogota) 90, ∞ Bogota Susan 50, ∞ Jane Prague

8. Memory Optimized Tables and Indexes Timestamps Chain ptrs Name City Hash index on City Hash index on Name f(Prague) f(John) 100, ∞ John Prague 90, ∞ Bogota Susan Jane Prague 50, ∞ T100: INSERT (John, Prague)

9. Memory Optimized Tables and Indexes Timestamps Chain ptrs Name City Hash index on City Hash index on Name 100, ∞ John Prague 90, ∞ Bogota Susan Jane Prague 50, ∞ 90, 150 T150: DELETE (Susan, Bogota)

10. Memory Optimized Tables and Indexes Timestamps Chain ptrs Name City Hash index on City Hash index on Name 200, ∞ John Beijing f(John) 100, 200 100, ∞ John Prague f(Beijing) 90, 150 Bogota Susan Jane Prague 50, ∞ T200: UPDATE (John, Prague) to (John, Beijing)

11. Memory Optimized Tables and Indexes Timestamps Chain ptrs Name City Hash index on City Hash index on Name f(Jane) 200, ∞ John Beijing f(Prague) f(John) 100, 200 John Prague f(Beijing) 90, 150 Bogota Susan Jane Prague 50, ∞ T250: Garbage collection

12. Storage: Data and Delta Files TS (del) TS (ins) TS (ins) TS (ins) RowId RowId RowId TS (del) TS (del) 0 100 Transaction Timestamp Range • Data files • ~128MB in size, write 256KB chunks at a time • Stores only the inserted rows (i.e. table content) • Chronologically organized streams of row versions • Delta files • File size is not constant, write 4KB chunks at a time. • Stores IDs of deleted rows Data File Row pay load TS (ins) RowId TableId Row pay load TS (ins) RowId TableId Row pay load TS (ins) RowId TableId Data file contains rows inserted within a given transaction range Checkpoint File Pair Delta File Delta file contains deleted rows within a given transaction range

13. Populating Data/Delta files Del Tran1(row TS150) Log in disk Table Del Tran2(row TS 450) Del Tran3(row TS 250) Insert into T1 Del Tran2(TS 450) Insert into Hekaton T1 SQL Transaction log Del Tran3(TS 250) Del Tran1(TS150) Offline Checkpoint Thread • Engine switches to new data file when the current file is full • Transaction does not span data files • Once a data file is closed, it becomes read-only • Row deletes are tracked in delta file • Files are append only Delete 150 TS New Inserts Delete 450 TS Delete 250 TS Memory-optimized Table Filegroup Range 500- Range 100-199 Range 200-299 Range300-399 Range 400-499 Data file with rows generated in timestamp range IDs of Deleted Rows (height indicates % deleted)

14. Merge Operation Files as of Time 500 Files as of Time 600 Memory-optimized data Filegroup Memory-optimized data Filegroup Range 400-499 Range 100-199 Range 200-299 Range 300-399 Range 500-599 Range 400-499 Range 200-399 Range 100-199 Range 200-299 Range 200-299 Range 300-399 Range 300-399 Merge 200-399 Deleted Files IDs of Deleted Rows (height indicates % deleted) Files Under Merge Data file with rows generated in timestamp range

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

16. In-Memory DW

17. Columnstore Storage Model • Data stored column-wise • Each page stores data from a single column • Highly compressed • Each column can be accessed independently

18. In-Memory DW IndexProcessing an Example

19. Horizontally PartitionRow Groups

20. Vertical PartitionSegments

21. Some Compress More than Others Compress Each Segment

22. Fetch Only Needed ColumnsSegment Elimination

23. Fetch Only Needed SegmentsSegment Elimination

24. Updatable Columnstore Index • Table consists of column store and row store • DML (update, delete, insert) operations leverage delta store • SELECT • Unifies data from Column and Row stores - internal UNION operation. • “Tuple mover” converts data into columnar format once segment is full (1M of rows) • REORGANIZE statement forces tuple mover to start. C1 C4 C5 C6 C3 C2 Delta (row) store C1 C4 C5 C6 C3 C2 Column Store tuple mover

25. Structure of In-Memory DWHow It Works Partition • 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 DeletedBitmap ColumnStore Row Store

26. Structure of In-Memory DWHow It Works (cont'd) Partition • DELETE • Rows are marked in the deleted bitmap • UPDATE • Delete plus insert • Most data is in columnstore format DeletedBitmap ColumnStore Row Store

27. Columnstoreusage recommendations • Use NCSI • If you need constraints and forcing uniqueness • Use CCI in other cases • Use for big tables • JOINs on string columns is still slow! Use integer keys instead • Tune batch sizes for BULK INSERTS • You don’t need any special maintenance if your data loading is tuned and you don’t have many deletes

28. Resource Governor

29. Resource Governor components

30. Resource Pools • Represents physical resources of server • Can have one or more workloads assigned to pool • Pool divided into shared and non-shared • Pools control min/max for CPU/memory and now IOPS CREATE RESOURCE POOL pool_name [ WITH     ( [ MIN_CPU_PERCENT = value ]     [ [ , ] MAX_CPU_PERCENT = value ]      [ [ , ] CAP_CPU_PERCENT = value ]      [ [ , ] AFFINITY {SCHEDULER = AUTO | (Scheduler_range_spec) | NUMANODE = (NUMA_node_range_spec)} ]     [ [ , ] MIN_MEMORY_PERCENT = value ]     [ [ , ] MAX_MEMORY_PERCENT = value ]     [ [ , ] MIN_IOPS_PER_VOLUME = value ]     [ [ , ] MAX_IOPS_PER_VOLUME = value ]) ]

31. Resource Pools • Minimums across all resource pools can not exceed 100 percent • Non-shared portion provides minimums • Shared portion provides maximums • Pools can define min/max for CPU/Memory/IOPS • Mins defined non-shared • Max defined shared

32. Steps to implement Resource Governor • Create workload groups • Create function to classify requests into workload group • Register the classification function in the previous step with the Resource Governor • Enable Resource Governor • Monitor resource consumption for each workload group • Use monitor to establish pools • Assign workload group to pool

33. Resource Governor scenarios • Scenario 1: I just got a new version of SQL Server and would like to make use of resource governor. How can I use it in my environment? • Scenario 2 (based on Scenario 1): Based on monitoring results I would like to see an event any time a query in the ad-hoc group (groupAdhoc) runs longer than 30 seconds. • Scenario 3 (based on Scenario 2): I want to further restrict the ad-hoc group so it does not exceed 50 percent of CPU usage when all requests are cumulated.

34. Monitoring Resource Governor • System views • sys.resource_governor_resource_pools • sys.resource_governor_configuration • DMVs • sys.dm_resource_governor_resource_pools • sys.dm_resource_governor_resource_pool_volumes • sys.dm_resource_governor_configuration • New performance counters • SQL Server:Resource Pool Stats • SQL Server:Workload group • XEvents • file_read_enqueued • file_write_enqueued

35. Complete and consistent data platform SQL Server 2014 Mission-critical performance Faster insights from any data Platform for hybrid cloud CUSTOMER SERVICE PROVIDER WINDOWS AZURE 1 CONSISTENTPLATFORM Development Management Data Identity Virtualization

36. Споделете вашата обратна връзка за тази сесия и за цялостната организация на конференцията http://aka.ms/incharge и участвайте в томболата за HTC 8S и други награди!