Expert SQL Server In-Memory OLTP

Front Matter ....Pages i-xxi Why In-Memory OLTP? (Dmitri Korotkevitch)....Pages 1-7 In-Memory OLTP Objects (Dmitri Korotkevitch)....Pages 9-26 Memory-Optimized Tables (Dmitri Korotkevitch)....Pages 27-39 Hash Indexes (Dmitri Korotkevitch)....Pages 41-61 Nonclustered Indexes (Dmitri Korotkevitch)....Pages 63-85 Memory Consumers and Off-Row Storage (Dmitri Korotkevitch)....Pages 87-98 Columnstore Indexes (Dmitri Korotkevitch)....Pages 99-118 Transaction Processing in In-Memory OLTP (Dmitri Korotkevitch)....Pages 119-137 In-Memory OLTP Programmability (Dmitri Korotkevitch)....Pages 139-164 Data Storage, Logging, and Recovery (Dmitri Korotkevitch)....Pages 165-186 Garbage Collection (Dmitri Korotkevitch)....Pages 187-197 Deployment and Management (Dmitri Korotkevitch)....Pages 199-224 Utilizing In-Memory OLTP (Dmitri Korotkevitch)....Pages 225-265 Back Matter ....Pages 267-304 Contents at a Glance 5 Contents 7 About the Author 13 About the Technical Reviewer 14 Acknowledgments 15 Introduction 16 Chapter 1: Why In-Memory OLTP? 19 Background 19 In-Memory OLTP Engine Architecture 21 In-Memory OLTP and Other In-Memory Databases 23 Oracle 23 IBM DB2 24 SAP HANA 24 Summary 25 Chapter 2: In-Memory OLTP Objects 26 Preparing a Database to Use In-Memory OLTP 26 Creating Memory-Optimized Tables 28 Working with Memory-Optimized Tables 31 In-Memory OLTP in Action: Resolving Latch Contention 35 Summary 43 Chapter 3: Memory-Optimized Tables 44 Disk-Based vs. Memory-Optimized Tables 44 Introduction to Multiversion Concurrency Control 48 Data Row Format 51 Native Compilation of Memory-Optimized Tables 52 Memory-Optimized Tables: Surface Area and Limitations 53 Supported Data Types 53 Table Features 54 Database-Level Limitations 54 High Availability Technologies Support 55 SQL Server 2016 Features Support 55 Summary 56 Chapter 4: Hash Indexes 57 Hashing Overview 57 Much Ado About Bucket Count 58 Bucket Count and Performance 59 Choosing the Right Bucket Count 64 Hash Indexes and SARGability 65 Statistics on Memory-Optimized Tables 69 Summary 76 Chapter 5: Nonclustered Indexes 78 Working with Nonclustered Indexes 78 Creating Nonclustered Indexes 79 Using Nonclustered Indexes 79 Nonclustered Index Internals 84 Bw-Tree Overview 84 Index Pages and Delta Records 86 Obtaining Information About Nonclustered Indexes 88 Index Design Considerations 91 Data Modification Overhead 91 Hash Indexes vs. Nonclustered Indexes 96 Summary 100 Chapter 6: Memory Consumers and Off-Row Storage 101 Varheaps 101 In-Row and Off-Row Storage 104 Performance Impact of Off-Row Storage 107 Summary 112 Chapter 7: Columnstore Indexes 113 Column-Based Storage Overview 113 Row-Based vs. Column-Based Storage 114 Columnstore Indexes Overview 115 Clustered Columnstore Indexes 118 Performance Considerations 123 Columnstore Indexes Limitations 126 Catalog and Data Management Views 127 sys.dm_db_column_store_row_group_physical_stats 127 sys.column_store_segments 128 sys.column_store_dictionaries 130 Summary 131 Chapter 8: Transaction Processing in In-Memory OLTP 133 ACID, Transaction Isolation Levels, and Concurrency Phenomena Overview 133 Transaction Isolation Levels in In-Memory OLTP 136 Cross-Container Transactions 142 Transaction Lifetime 143 Referential Integrity Enforcement 148 Summary 150 Chapter 9: In-Memory OLTP Programmability 152 Native Compilation Overview 152 Natively Compiled Modules 157 Natively Compiled Stored Procedures 157 Natively Compiled Triggers and User-Defined Functions 159 Supported T-SQL Features 160 Control Flow 160 Operators 161 Query Surface Area 161 Built-in Functions 162 Atomic Blocks 163 Optimization of Natively Compiled Modules 165 Interpreted T-SQL and Memory-Optimized Tables 166 Performance Comparison 167 Stored Procedures Performance 167 Scalar User-Defined Function Performance 172 Memory-Optimized Table Types and Variables 174 Summary 177 Chapter 10: Data Storage, Logging, and Recovery 178 Data Storage 178 Checkpoint Files States 180 PRECREATED State 181 UNDER CONSTRUCTION State and CHECKPOINT Process 181 ACTIVE State 183 MERGE TARGET State and Merge Process 185 WAITING FOR LOG TRUNCATION State 185 Recovery 186 Transaction Logging 187 Table Alteration 191 Summary 199 Chapter 11: Garbage Collection 200 Garbage Collection Process Overview 200 Garbage Collection–Related Data Management Views 205 Exploring the Garbage Collection Process 206 Summary 210 Chapter 12: Deployment and Management 211 Hardware Considerations 211 CPU 212 I/O Subsystem 212 Memory 213 Estimating the Amount of Memory for In-Memory OLTP 214 Administration and Monitoring Tasks 216 Limiting the Amount of Memory Available to In-Memory OLTP 216 Monitoring Memory Usage for Memory-Optimized Tables 218 Monitoring In-Memory OLTP Transactions 222 Collecting Execution Statistics for Natively Compiled Stored Procedures 224 In-Memory OLTP and Query Store Integration 227 Metadata Changes and Enhancements 228 Catalog Views 228 sys.hash_indexes 228 sys.memory_optimized_tables_internal_attributes 228 Changes in Other Catalog Views 229 Data Management Views 230 Object and Index Statistics 230 Memory Usage Statistics 231 Transaction Management 232 Garbage Collection 232 Checkpoint 233 Extended Events and Performance Counters 233 Summary 236 Chapter 13: Utilizing In-Memory OLTP 237 Design Considerations for Systems Utilizing In-Memory OLTP 237 Off-Row Storage 238 Unsupported Data Types 242 Indexing Considerations 244 Maintainability and Management Overhead 250 Using In-Memory OLTP in Systems with Mixed Workloads 251 Thinking Outside the In-Memory Box 264 Importing Batches of Rows from Client Applications 264 Using Memory-Optimized Objects as Replacements for Temporary and Staging Tables 267 Using In-Memory OLTP as Session or Object State Store 271 Summary 277 Appendix A: Memory Pointer Management 278 Memory Pointer Management 278 Summary 280 Appendix B: Page Splitting and Page Merging in Nonclustered Indexes 281 Internal Maintenance of Nonclustered Indexes 281 Page Splitting 281 Page Merging 283 Summary 284 Appendix C: Analyzing the States of Checkpoint Files 285 sys.dm_db_xtp_checkpoint_files View 285 The Lifetime of Checkpoint Files 286 Summary 296 In-Memory OLTP Migration Tools 297 “Transaction Performance Analysis Overview” Report 297 Memory Optimization and Native Compilation Advisors 302 Summary 306 Index 307 Take a deep dive into one of the most significant SQL Server features–support for In-Memory Online Transaction Processing. The latest developments are covered, including support of off-row storage, columnstore indexes and operational analytics, changes in programmability and native compilation, and more. This book describes the architecture and internals of the In-Memory OLTP Engine and explains how to develop, deploy, and maintain systems using it. With it you can dramatically increase transaction throughput to handle thousands of transactions per second supporting millions of customers. Learn the architecture and the internals of In-Memory OLTP in order to recognize when technology can make a difference. Recognize opportunities for In-Memory OLTP in new development and understand how to benefit from it in existing systems. Don’t be without Dmitri Korotkevitch and the deep expertise he imparts in __Expert SQL Server In-Memory OLTP, 2nd Edition__ as you move forward in using SQL Server’s In-Memory OLTP technology. Dmitri Korotkevitch is the five-star author of __Pro SQL Server Internals__, and brings the same combination of clear thinking and deep expertise to help you in this second edition. The book: • Explains In-Memory OLTP internals, architecture and programmability, including data storage, indexing, multi-version concurrency control, transaction logging and recovery, and native compilation • Covers SQL Server 2016 technology enhancements, including columnstore indexes and off-row storage • Guides in using In-Memory OLTP in new development and existing systems What You’ll Learn • Grasp how SQL Server stores and works with data in memory-optimized tables • Properly design and index memory-optimized tables • Plan successful deployments, including the required memory size and I/O configuration • Accelerate T-SQL processing through the creation of natively compiled modules • Monitor and report on the benefits and performance of your In-Memory OLTP solutions • Benefit from the technology in existing systems and in the systems with the mixed workload Who This Book Is For Application developers and database administrators who create and manage online transaction processing (OLTP) systems; in particular, those wanting to take advantage of SQL Server’s new offering of in-memory OLTP to dramatically improve performance and throughput of their systems.