SQLintersection Session: Tuesday, 9:45am –10:45am Managing In-Memory OLTP – a DBA Field Guide

1. SQLintersectionSession: Tuesday, 9:45am–10:45amManaging In-Memory OLTP – a DBA Field Guide Justin Randall jrandall@sentryone.com

2. Speaker: Justin Randall • Senior Professional Services Consultant • 30+ years experience as a data professional • DB2, Sybase, SQL Server • Blog: http://blogs.sentryone.com/author/JustinRandall • Twitter: @jh_randall

3. Reminder: Intersect with Speakers and Attendees • Tweet tips and tricks that you learn and follow tweets posted by your peers! • Follow: #SQLintersection and/or #DEVintersection • Doors open at 7:00 pm • Trivia game starts at 7:30 pm Winning team receives something fun! • Raffle at the end of the night Lots of great items to win including a seat in a SQLskills Immersion Event! • The first round of drinks is sponsored by SentryOneand SQLskills • Join us – TUESDAY Evening – for SQLafterDark &

4. Overview • Introduction In-Memory OLTP in SQL Server 2016 has been reborn. Many limitations within the previous versions have been removed, making it a viable option for a variety of workloads. In this session we  will highlight critical issues in implementing and managing SQL Server 2016 instances and databases containing In-Memory tables. • Agenda • The In-Memory OLTP technology and its challenges • Memory Optimized Tables Basics • Managing Memory • Managing Storage • Backup, Restore, and Recovery • DBA Checklist

5. The Technology • Tables and Indexes fully in memory, always • Durable and Non-Durable (schema-only) • Multi-Version Concurrency Control (Optimistic Concurrency ) • Row versioning vs. locks & latches • Operations on memory-optimized tables are still logged, but less so • Natively compiled tables and table variables • New index structures • Natively compiled modules (optional) • Stored Procedures, triggers, and user-defined scalar functions

6. The DBA’s Challenges • Managing Expectations • Choosing the right workload • Managing Memory • Managing Storage • Recovery Time • Portability

7. SQL Server Requirements for In-Memory OLTP • SQL Server 2016 SP1 (or later), any edition • Enough memory to hold the data in memory-optimized tables and indexes, as well as additional memory to support the online workload. • When running SQL Server in a VM, allocate sufficient virtual memory to support memory-optimized tables and indexes. • Free disk space 2X the size of your durable memory-optimized tables. • Processors need to support the instruction cmpxchg16b. All modern 64-bit processors do so

8. Target Workloads * In-Memory OLTP Common Workload Patterns and Migration Considerations White Paper

9. Memory-Optimized TablesRow-based, latch and lock-free structures in the SQL Server engine, designed for transactional system performance gains • Speed Gains Achieved Through: • Entirely in-memory storage, all the time • Multi-Version Concurrency Control • row versioning replaces latches and locks • Natively Compiled Modules (optional): • Stored Procedures, Triggers, and User-Defined Scalar Functions • Durability Options • Schema Only (non-durable) • Schema and Data (durable) • Delayed Durability

10. Memory-Optimized Table Limitations • Cross-database transactions not allowed • Cannot access linked servers • CHECKDB & CHECKTABLE ignore in-memory tables • Bulk logging and minimal logging not supported • FK constraints must reference a PK, not a unique constraint.The referenced table must also be memory-optimized • Legacy LOB, XML, and CLR data types are not allowed • IDENTITY columns SEED and INCREMENT must equal 1

11. Memory-Optimized Table IndexesBasics • Exist only in active memory. • Rebuilt when the database is brought back online. • SQL UPDATE statements that change indexes are not logged. • Entries in a memory-optimized index contain a direct memory address to the row in the table. • Memory-optimized indexes have no fixed pages. • No traditional fragmentation within a page, so no fill factor. Resource: In-Memory OLTP – Row Structure and Indexes - http://bit.ly/2A1sj8F

12. Memory-Optimized Tables & Indexes Demo

13. Memory ConsumptionMemory Optimized Tables Reside Fully in Memory • data access at speed of memory means fast – Yeah!! • modifications to data create row versions, which consumes more memory – Uh Oh!! • in-memory objects compete with: • data (buffer pool) • plan caches • internal structures for server memory space • no limit on the size of memory-optimized tables in SQL 2016

14. Memory ConsumptionMemory Allocation Over Time

15. Memory Requirements Calculate estimated memory consumption • memory for the table • memory for the indexes • hash indexes • non-clustered indexes • memory for row-versioning • memory for table variables • memory for growth

16. Monitoring Memory Consumption • SSMS Reports • Consumption at the database level • DMVs • sys.dm_db_xtp_table_memory_stats: user tables, indexes & system objects • sys.dm_xtp_system_memory_consumers: internal system structures • sys.dm_os_memory_objects: run-time structures • sys.dm_os_memory_clerks: In-Memory OLTP Engine

17. Memory Management Strategies • Do the math! https://msdn.microsoft.com/en-us/library/dn282389.aspx • Bind the database to a resource pool https://msdn.microsoft.com/en-us/library/dn465873.aspx • Monitor and troubleshoot memory usage https://msdn.microsoft.com/en-us/library/dn465869.aspx • Consider Application-level partitioning for larger tables https://msdn.microsoft.com/en-US/library/dn133171.aspx

18. Manage Memory Consumption Demo

19. Managing Storage • File & File Group Configuration • Storage Capacity • Storage Throughput (IOPS) • Increased transaction throughput may mean log file I/O pressure • Effect on recovery time • Data Compression is not supported • Enable Instant File Initialization

20. Backup, Restore, and Recovery • Full, Differential, and Transaction Log backups fully support databases with durable in-memory tables • The size of full backups is typically larger than its size in memory, but smaller than on-disk storage • Piecemeal restores are supported • Memory-optimized tables must be loaded into memory before the database is available for use, increasing recovery time • Attaching a database without a log file is not supported

21. DBA In-Memory OLTP Checklist • Consider workload patterns benefitting from in-memory OLTP • Carefully calculate memory requirements • Plan storage requirements • Account for impact on database restore and recovery • Account for impact on database portability

22. Review • Overview • In SQL Server 2016 & 2017, In-Memory OLTP is a viable option for some scenarios. DBAs & developers need to understand the complexities to safely implementing this technology • In-Memory OLTP Considerations • Is In-Memory OLTP an appropriate option for your use case(s)? • Memory consumption, monitoring, and management • Storage management • Consider impact on backup, restore, and recovery

23. SQL Server 2016 Improvements • Use ALTER TABLE to: • Add and Drop columns, indexes, and constraints • Modify column definitions • Change the number of hash buckets in a hash index • Most ALTER TABLE operations can be multi-threaded and are log-optimized • Columnstore Indexes are supported • Create memory-optimized table types (table variables) • Stored only in memory • Not stored in tempdb, do not use any tempdb resources • Avoid contention on database PFS and SGAM pages

24. SQL Server 2017 Improvements • Computed column support, including indexes on computed columns • Full support for JSON functions in natively compiled modules • TSQL enhancements for natively compiled modules • CASE, CROSS APPLY, and TOP (N) WITH TIES • Performance and language enhancements • sp_spaceused is supported • sp_rename support for memory-optimized tables and natively compiled modules • Limitation of 8 indexes on memory-optimized tables is eliminated • ALTER TABLE against memory-optimized tables is faster • Tlog redo of memory-optimized tables is now done in parallel • improves recovery times • Increases sustained throughput of availability group configuration

25. SQL Server 2016 In-memory OLTP (Books Online)https://msdn.microsoft.com/en-us/library/dn133186.aspx Inside SQL Server In-Memory OLTP (Bob Ward video)https://www.youtube.com/watch?v=P9DnjQqE0Gc In-Memory OLTP Common Workload Patterns and Migration Considerations https://msdn.microsoft.com/en-us/library/dn673538.aspx Quick Start 1: In-memory OLTP Technologies for faster T-SQL Performancehttps://msdn.microsoft.com/en-us/library/mt694156.aspx Memory-Optimized Tableshttps://msdn.microsoft.com/en-us/library/dn133165.aspx Resources

26. Managing Memory for In-Memory OLTPhttps://msdn.microsoft.com/en-us/library/dn465872.aspx Creating and Managing Storage for Memory-Optimized Objectshttps://msdn.microsoft.com/en-us/library/dn133174.aspx Backup, Restore, and Recovery of Memory Optimized Tableshttps://msdn.microsoft.com/en-us/library/dn553123.aspx SQL Server Support for In-Memory OLTPhttps://msdn.microsoft.com/en-us/library/dn133189.aspx Migrating to In-Memory OLTPhttps://msdn.microsoft.com/en-us/library/dn247639.aspx In-Memory OLTP posts on Ned Otter's bloghttps://nedotter.com/archive/category/in-memory-oltp Resources

27. Questions? Don’t forget to complete an online evaluation! Managing In-Memory OLTP – a DBA Field Guide Your evaluation helps organizers build better conferences and helps speakers improve their sessions. Thank you!

28. Save the Date www.SQLintersection.com Week of December 2, 2018 We’re back in Vegas baby!