Contents
Background 1
In-Memory OLTP Engine Architecture 3
In-Memory OLTP and Other In-Memory Databases 5
Oracle 5
IBM DB2 6
SAP HANA 6
Summary 7
Preparing a Database to Use In-Memory OLTP 9
Creating Memory-Optimized Tables 11
Working with Memory-Optimized Tables 14
In-Memory OLTP in Action: Resolving Latch Contention 18
Summary 26
Disk-Based vs Memory-Optimized Tables 27
Introduction to Multiversion Concurrency Control 31
Data Row Format 34
Native Compilation of Memory-Optimized Tables 35
Memory-Optimized Tables: Surface Area and Limitations 36
Supported Data Types 36
Table Features 37
Database-Level Limitations 37
High Availability Technologies Support 38
SQL Server 2016 Features Support 38
Summary 39
Hashing Overview 41
Much Ado About Bucket Count 42
Bucket Count and Performance 43
Choosing the Right Bucket Count 48
Hash Indexes and SARGability 49
Statistics on Memory-Optimized Tables 53
Summary 60
Working with Nonclustered Indexes 63
Creating Nonclustered Indexes 64
Using Nonclustered Indexes 64
Nonclustered Index Internals 69
Bw-Tree Overview 69
Index Pages and Delta Records 71
Obtaining Information About Nonclustered Indexes 73
Index Design Considerations 76
Data Modification Overhead 76
Hash Indexes vs Nonclustered Indexes 81
Summary 85
Varheaps 87
In-Row and Off-Row Storage 90
Performance Impact of Off-Row Storage 93
Summary 98
Column-Based Storage Overview 99
Row-Based vs Column-Based Storage 100
Columnstore Indexes Overview 101
Clustered Columnstore Indexes 104
Performance Considerations 109
Columnstore Indexes Limitations 112
Catalog and Data Management Views 113
sysdm_db_column_store_row_group_physical_stats 113
syscolumn_store_segments 114
syscolumn_store_dictionaries 116
Summary 117
ACID, Transaction Isolation Levels, and Concurrency Phenomena Overview 119
Transaction Isolation Levels in In-Memory OLTP 122
Cross-Container Transactions 128
Transaction Lifetime 129
Referential Integrity Enforcement 134
Summary 136
Native Compilation Overview 139
Natively Compiled Modules 144
Natively Compiled Stored Procedures 144
Natively Compiled Triggers and User-Defined Functions 146
Supported T-SQL Features 147
Atomic Blocks 150
Optimization of Natively Compiled Modules 152
Interpreted T-SQL and Memory-Optimized Tables 153
Performance Comparison 154
Stored Procedures Performance 154
Scalar User-Defined Function Performance 159
Memory-Optimized Table Types and Variables 161
Summary 164
Data Storage 165
Checkpoint Files States 167
Recovery 173
Transaction Logging 174
Table Alteration 178
Summary 186
Garbage Collection Process Overview 187
Garbage Collection–Related Data Management Views 192
Exploring the Garbage Collection Process 193
Summary 197
Hardware Considerations 199
CPU 200
I/O Subsystem 200
Memory 201
Estimating the Amount of Memory for In-Memory OLTP 202
Administration and Monitoring Tasks 204
Limiting the Amount of Memory Available to In-Memory OLTP 204
Monitoring Memory Usage for Memory-Optimized Tables 206
Monitoring In-Memory OLTP Transactions 210
Collecting Execution Statistics for Natively Compiled Stored Procedures 212
In-Memory OLTP and Query Store Integration 215
Metadata Changes and Enhancements 216
Catalog Views 216
Data Management Views 218
Extended Events and Performance Counters 221
Summary 224
Design Considerations for Systems Utilizing In-Memory OLTP 225
Off-Row Storage 226
Unsupported Data Types 230
Indexing Considerations 232
Maintainability and Management Overhead 238
Using In-Memory OLTP in Systems with Mixed Workloads 239
Thinking Outside the In-Memory Box 252
Importing Batches of Rows from Client Applications 252
Using Memory-Optimized Objects as Replacements for Temporary and
Staging Tables 255
Using In-Memory OLTP as Session or Object State Store 259
Summary 265
■■Appendix A: Memory Pointer Management 267
Memory Pointer Management 267
Summary 269
■■ Appendix B: Page Splitting and Page Merging in
Nonclustered Indexes 271
Internal Maintenance of Nonclustered Indexes 271
Page Splitting 271
Page Merging 273
Summary 274
■■Appendix C: Analyzing the States of Checkpoint Files 275
sysdm_db_xtp_checkpoint_files View 275
The Lifetime of Checkpoint Files 276
Summary 286
■■Appendix D: In-Memory OLTP Migration Tools 287
“Transaction Performance Analysis Overview” Report 287
Memory Optimization and Native Compilation Advisors 292
Summary 296
Index 297
The year 2016 was delightful for the SQL Server community—we put our hands on the new SQL Server build. This was quite a unique release; for the first time in more than ten years, the new version did not focus on specific technologies. In SQL Server 2016, you can find enhancements in all product areas, such as programmability, high availability, administration, and BI.
I, personally, was quite excited about all the enhancements in In-Memory OLTP. I really enjoyed this technology in SQL Server 2014; however, it had way too many limitations. This made it a niche technology and prevented its widespread adoption. In many cases, the cost of the required system refactoring put the first release of In-Memory OLTP in the “it’s not worth it” category.
I was incredibly happy that the majority of those limitations were removed in SQL Server 2016. There are still some, but they are not anywhere near as severe as in the first release. It is now possible to migrate systems into memory and start using the technology without significant code and database schema changes.
I would consider this simplicity, however, a double-edged sword. While it can significantly reduce the time and cost of adopting the technology, it can also open the door to incorrect decisions and suboptimal implementations. As with any other technology, In-Memory OLTP has been designed for a specific set of tasks, and it can hurt the performance of the systems when implemented incorrectly. Neither is it a “set it and forget it” type of solution; you have to carefully plan for it before implementing it and maintain it after the deployment.
In-Memory OLTP is a great tool, and it can dramatically improve the performance of systems. Nevertheless, you need to understand how it works under the hood to get the most from it. The goal for this book is to provide you with such an understanding. I will explain the internals of the In-Memory OLTP Engine and its components. I believe that knowledge is the cornerstone of a successful In-Memory OLTP implementation, and this book will help you make educated decisions on how and when to use the technology.
If you read my Pro SQL Server Internals book (Apress, 2016), you will notice some familiar content from there. However, this book is a much deeper dive into In-Memory OLTP, and you will find plenty of new topics covered. You will also learn how to address some of In-Memory OLTP’s limitations and how to benefit from it in existing systems when full in-memory migration is cost-ineffective.
Even though this book covers In-Memory OLTP in SQL Server 2016, the content should also be valid for the SQL Server 2017 implementation. Obviously, check what technology limitations were lifted there. Finally, I would like to thank you for choosing this book and for your trust in me. I hope that you will enjoy reading it as much as I enjoyed writing it.
How This Book Is Structured
This book consists of 13 chapters and is structured in the following way:
• Chapter 1 and Chapter 2 are the introductory chapters, which will provide you with an overview of the technology and show how In-Memory OLTP objects work together.
• Chapter 3, Chapter 4, and Chapter 5 explain how In-Memory OLTP stores and works with data in memory.
• Chapter 6 shows how In-Memory OLTP allocates memory for internal objects and works with off-row columns. I consider this as one of the most important topics for successful in-memory OLTP migrations.
• Chapter 7 covers columnstore indexes that help you to support operational analytics workloads.
• Chapter 8 explains how In-Memory OLTP handles concurrency in a multi-user environment.
• Chapter 9 talks about native compilation and the programmability aspect of the technology.
• Chapter 10 demonstrates how In-Memory OLTP persists data on disk and how it works with the transaction log.
• Chapter 11 covers the In-Memory OLTP garbage collection process.
• Chapter 12 discusses best practices for In-Memory OLTP deployments and shows how to perform common database administration tasks related to In-Memory OLTP.
• Chapter 13 demonstrates how to address some of the In-Memory OLTP surface area limitations and how to benefit from In-Memory OLTP components without moving all the data into memory.
The book also includes four appendixes.
• Appendix A explains how In-Memory OLTP works with memory pointers in a multi-user environment.
• Appendix B covers how the page splitting and merging processes are implemented.
• Appendix C shows you how to analyze the state of checkpoint file pairs and navigates you through their lifetime.
• Appendix D discusses SQL Server tools and wizards that can simplify In-Memory OLTP migration.
Why In-Memory OLTP?
This introductory chapter explains the importance of in-memory databases and the problems they address. It provides an overview of the Microsoft In-Memory OLTP implementation (code name Hekaton) and its design goals. It discusses the high-level architecture of the In-Memory OLTP Engine and how it is integrated into SQL Server.
Finally, this chapter compares the SQL Server in-memory database product with several other solutions available.
Background
Way back when SQL Server and other major databases were originally designed, hardware was expensive. Servers at that time had just one or very few CPUs and a small amount of installed memory. Database servers had to work with data that resided on disk, loading it into memory on demand.
The situation has changed dramatically since then. During the last 30 years, memory prices have dropped by a factor of 10 every 5 years. Hardware has become more affordable. It is now entirely possible to buy a server with 32 cores and 1TB of RAM for less than $50,000. While it is also true that databases have become larger, it is often possible for active operational data to fit into the memory.
Obviously, it is beneficial to have data cached in the buffer pool. It reduces the load on the I/O subsystem and improves system performance. However, when systems work under a heavy concurrent load, this is often not enough to obtain the required throughput. SQL Server manages and protects page structures in memory, which introduces large overhead and does not scale well. Even with row-level locking, multiple sessions cannot modify data on the same data page simultaneously and must wait for each other.
Perhaps the last sentence needs to be clarified. Obviously, multiple sessions can modify data rows on the same data page, holding exclusive (X) locks on different rows simultaneously. However, they cannot update physical data page and row objects simultaneously because this could corrupt the in-memory page structure. SQL Server addresses this problem by protecting pages with latches. Latches work in a similar manner to locks, protecting internal SQL Server data structures on the physical level by serializing access to them, so only one thread can update data on the data page in memory at any given point of time.
In the end, this limits the improvements that can be achieved with the current database engine’s architecture. Although you can scale hardware by adding more CPUs and cores, that serialization quickly becomes a bottleneck and a limiting factor in improving system scalability. Likewise, you cannot improve performance by increasing the CPU clock speed because the silicon chips would melt down. Therefore, the only feasible way to improve database system performance is by reducing the number of CPU instructions that need to be executed to perform an action.
Unfortunately, code optimization is not enough by itself. Consider the situation where you need to update a row in a table. Even when you know the clustered index key value, that operation needs to traverse the index tree, obtaining latches and locks on the data pages and a row. In some cases, it needs to update nonclustered indexes, obtaining the latches and locks there. All of that generates log records and requires writing them and the dirty data pages to disk.
All of those actions can lead to a hundred thousand or even millions of CPU instructions to execute. Code optimization can help reduce this number to some degree, but it is impossible to reduce it dramatically without changing the system architecture and the way the system stores and works with data.
These trends and architectural limitations led the Microsoft team to the conclusion that a true in-memory solution should be built using different design principles and architecture than the classic SQL Server Database Engine. The original concept was proposed at the end of 2008, serious planning and design started in 2010, actual development began in 2011, and the technology was finally released to the public in SQL Server 2014.
The main goal of the project was to build a solution that would be 100 times faster than the existing SQL Server Database Engine, which explains the code name Hekaton (Greek for “100”). This goal has yet to be achieved; however, it is not uncommon for In-Memory OLTP to provide 20 to 40 times faster performance in certain scenarios.
It is also worth mentioning that the Hekaton design has been targeted toward OLTP workloads. As we all know, specialized solutions designed for particular tasks and workloads usually outperform general-purpose systems in the targeted areas. The same is true for In-Memory OLTP. It shines with large and busy OLTP systems that support hundreds or even thousands of concurrent transactions. At the same time, the original release of In-Memory OLTP in SQL Server 2014 did not work well for a data warehouse workload, where other SQL Server technologies outperformed it.
The situation changes with the SQL Server 2016 release. The second release of In-Memory OLTP supports columnstore indexes, which allow you to run real-time operation analytics queries against hot OLTP data. Nevertheless, the technology is not as mature as disk-based column-based storage, and you should not consider it an in-memory data warehouse solution.
in-memory data warehouse solution.
In-Memory OLTP has been designed with the following goals:
• Optimize data storage for main memory: Data in In-Memory OLTP is not stored on disk-based data pages, and it does not mimic a disk-based storage structure when loaded into memory. This permits the elimination of the complex buffer pool structure and the code that manages it. Moreover, regular (non-columnstore) indexes are not persisted on disk, and they are re-created upon startup when the data from memory-resident tables is loaded into memory.
• Eliminate latches and locks: All In-Memory OLTP internal data structures are latch- and lock-free. In-Memory OLTP uses a multiversion concurrency control to provide transaction consistency. From a user standpoint, it behaves like the regular SNAPSHOT transaction isolation level; however, it does not use a locking or tempdb version store under the hood. This schema allows multiple sessions to work with the same data without locking and blocking each other and provides near-linear scalability of the system, allowing it to fully utilize modern multi-CPU/multicore hardware.
• Use native compilation: T-SQL is an interpreted language that provides great flexibility at the cost of CPU overhead. Even a simple statement requires hundreds of thousands of CPU instructions to execute. The In-Memory OLTP Engine addresses this by compiling row-access logic, stored procedures, and user-defined functions into native machine code.
The In-Memory OLTP Engine is fully integrated in the SQL Server Database Engine. You do not need to perform complex system refactoring, splitting data between in-memory and conventional database servers or moving all of the data from the database into memory. You can separate in-memory and disk data on a table-by-table basis, which allows you to move active operational data into memory, keeping other tables and historical data on disk. In some cases, that migration can even be done transparently to client applications.
This sounds too good to be true, and, unfortunately, there are still plenty of roadblocks that you may encounter when working with this technology. In SQL Server 2014, In-Memory OLTP supported just a subset of the SQL Server data types and features, which often required you to perform costly code and schema refactoring to utilize it. Even though many of those limitations have been removed in SQL Server 2016, there are still incompatibilities and restrictions you need to address.
You should also design the system considering In-Memory OLTP behavior and internal implementation to get the most performance improvements from the technology.
