SQL Server Internals: In-Memory OLTP Inside the SQL Server 2016 Hekaton Engine

_Hlk480613965 50 Introduction 16 1: What's Special About In-Memory OLTP? 20 Isn't In-Memory OLTP Just an Improved DBCC PINTABLE? 20 The In-Memory OLTP Component 21 Memory-optimized tables 22 Entirely in-memory storage 22 Row-based storage structure 23 Native compilation of tables 24 Natively compiled stored procedures 24 Concurrency improvements: the MVCC model 25 Indexes on memory-optimized tables 26 Data durability and recovery 27 Performance 29 What's New for In-Memory OLTP in SQL Server 2016 30 SQL Server In-Memory OLTP in Context 31 Summary 33 Additional Resources 34 Chapter 2: Creating and Accessing In-Memory OLTP Databases and Tables 35 Creating Databases 35 Azure SQL Database 37 Creating Tables 38 Durability 39 Indexes and constraints 39 Data type restrictions 41 Creating Table Types and Table Variables 42 Altering Memory-Optimized Tables 43 Accessing Memory-Optimized Tables with T-SQL 46 Interpreted T-SQL 46 T-SQL in natively compiled procedures 47 Memory Allocation for Memory-Optimized Tables and Indexes 48 Summary 52 Additional Resources 53 Chapter 3: Row Structure and Multi-Versioning 54 Row Structure 54 Row header 55 Payload area 56 Row Versions, Timestamps and the MVCC Model 56 Transaction IDs and timestamps 56 Row versions and transaction processing phases 58 Processing phase 59 Validation phase 62 Post-processing 62 Summary 64 Additional Resources 64 Chapter 4: Indexes on Memory-Optimized Tables 65 Nonclustered and Hash and Range Indexes 65 Hash indexes 67 Row organization 68 Choosing hash indexes 71 Determining the number of hash buckets 72 Range indexes 73 The Bw-tree 74 Index page structures 77 Internal index maintenance operations 78 Columnstore Indexes 86 Columnstore index basic architecture 86 Clustered columnstore indexes on memory-optimized tables 88 Updating columnstore indexes 88 Examining index metadata for a columnstore index on a memory-optimized table 91 Examining Index Metadata 95 Indexes on Temporal Tables 97 Summary 97 Additional Resources 98 Chapter 5: Transaction Processing 99 Transaction Scope 99 Transaction Isolation Levels 100 Isolation levels with disk-based tables 101 Isolation levels with memory-optimized tables 102 Rules for cross-container transactions (interpreted T-SQL) 103 READ COMMITTED cross-container transactions must specify a valid isolation level 104 SNAPSHOT cross-container transactions cannot access memory-optimized tables 106 REPEATABLE READ or SERIALIZABLE cross-container transactions must use SNAPSHOT 107 Choosing an isolation level for accessing memory- optimized tables 109 Monitoring Active Transactions 110 Transaction Processing Examples 111 Write-write conflicts 112 Read-write conflicts 113 Validation Phase 116 Validation phase, Step 1: Check for isolation level violations 117 SNAPSHOT isolation level violations 117 REPEATABLE READ isolation level violations 119 SERIALIZABLE isolation level violations 119 Validation of foreign keys 120 Validation phase, Step 2: Commit dependencies 120 Validation phase, Step 3: Logging 121 Post-processing 123 Garbage Collection of Rows in Memory 123 Summary 126 Additional Resources 127 Chapter 6: Logging, Checkpoint, and Recovery 128 Transaction Logging 129 Scalability: multiple concurrent log streams 129 Performance: reduced logging 130 Checkpoint 135 Anatomy of checkpoint files 136 Continuous checkpointing and checkpoint events 140 Checkpoint file metadata states 141 The checkpoint event 147 The threads and tasks comprising the full checkpoint process 149 Merging checkpoint files 150 Automatic merge 151 Garbage collection of checkpoint files 154 Recovery 155 Summary 156 Additional Resources 156 Chapter 7: Native Compilation of Tables and Native Modules 157 What Is Native Compilation? 157 Maintenance of DLLs 158 Native Compilation of Tables 159 Native Compilation of Modules 160 Parameter Sniffing 162 Compilation and Query Processing 163 Optimization of Natively Compiled Modules 164 Index access paths 165 Hash indexes 165 Range indexes 166 No Halloween protection 166 No automatic recompile 166 Best Practices with Natively Compiled Procedures 167 Performance Comparisons 168 Comparing performance for multi-row inserts 168 Performance analysis using DMV data 173 Summary 174 Additional Resources 174 Chapter 8: SQL Server Support and Manageability 175 Feature Support 176 Memory Allocation and Management 179 Using Resource Governor for memory management 180 Memory usage report 182 Metadata Enhancements and Additions 183 Catalog view enhancements 183 Dynamic Management Objects 184 Extended events 187 Performance counters 188 Best Practices for Designing Memory-Optimized Tables and Indexes 189 Best Practices for Managing Statistics on Memory-Optimized Tables 191 Migration Considerations 191 Common application bottlenecks that in-memory OLTP can resolve 192 Application requirements compatible with migration to in-memory OLTP 193 High volume of INSERTs 193 High volume of SELECTs 194 CPU-intensive operations 194 Extremely fast business transactions 195 Session state management 195 Unsuitable application requirements 196 Current applications 197 The migration process 198 Workload analysis and baselining 198 Summary 200 Additional Resources 201