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

Description

_Hlk480613965 Introduction 1: What's Special About In-Memory OLTP? Isn't In-Memory OLTP Just an Improved DBCC PINTABLE? The In-Memory OLTP Component Memory-optimized tables Entirely in-memory storage Row-based storage structure Native compilation of tables Natively compiled stored procedures Concurrency improvements: the MVCC model Indexes on memory-optimized tables Data durability and recovery Performance What's New for In-Memory OLTP in SQL Server 2016 SQL Server In-Memory OLTP in Context Summary Additional Resources Chapter 2: Creating and Accessing In-Memory OLTP Databases and Tables Creating Databases Azure SQL Database Creating Tables Durability Indexes and constraints Data type restrictions Creating Table Types and Table Variables Altering Memory-Optimized Tables Accessing Memory-Optimized Tables with T-SQL Interpreted T-SQL T-SQL in natively compiled procedures Memory Allocation for Memory-Optimized Tables and Indexes Summary Additional Resources Chapter 3: Row Structure and Multi-Versioning Row Structure Row header Payload area Row Versions, Timestamps and the MVCC Model Transaction IDs and timestamps Row versions and transaction processing phases Processing phase Validation phase Post-processing Summary Additional Resources Chapter 4: Indexes on Memory-Optimized Tables Nonclustered and Hash and Range Indexes Hash indexes Row organization Choosing hash indexes Determining the number of hash buckets Range indexes The Bw-tree Index page structures Internal index maintenance operations Columnstore Indexes Columnstore index basic architecture Clustered columnstore indexes on memory-optimized tables Updating columnstore indexes Examining index metadata for a columnstore index on a memory-optimized table Examining Index Metadata Indexes on Temporal Tables Summary Additional Resources Chapter 5: Transaction Processing Transaction Scope Transaction Isolation Levels Isolation levels with disk-based tables Isolation levels with memory-optimized tables Rules for cross-container transactions (interpreted T-SQL) READ COMMITTED cross-container transactions must specify a valid isolation level SNAPSHOT cross-container transactions cannot access memory-optimized tables REPEATABLE READ or SERIALIZABLE cross-container transactions must use SNAPSHOT Choosing an isolation level for accessing memory-optimized tables Monitoring Active Transactions Transaction Processing Examples Write-write conflicts Read-write conflicts Validation Phase Validation phase, Step 1: Check for isolation level violations SNAPSHOT isolation level violations REPEATABLE READ isolation level violations SERIALIZABLE isolation level violations Validation of foreign keys Validation phase, Step 2: Commit dependencies Validation phase, Step 3: Logging Post-processing Garbage Collection of Rows in Memory Summary Additional Resources Chapter 6: Logging, Checkpoint, and Recovery Transaction Logging Scalability: multiple concurrent log streams Performance: reduced logging Checkpoint Anatomy of checkpoint files Continuous checkpointing and checkpoint events Checkpoint file metadata states The checkpoint event The threads and tasks comprising the full checkpoint process Merging checkpoint files Automatic merge Garbage collection of checkpoint files Recovery Summary Additional Resources Chapter 7: Native Compilation of Tables and Native Modules What Is Native Compilation? Maintenance of DLLs Native Compilation of Tables Native Compilation of Modules Parameter Sniffing Compilation and Query Processing Optimization of Natively Compiled Modules Index access paths Hash indexes Range indexes No Halloween protection No automatic recompile Best Practices with Natively Compiled Procedures Performance Comparisons Comparing performance for multi-row inserts Performance analysis using DMV data Summary Additional Resources Chapter 8: SQL Server Support and Manageability Feature Support Memory Allocation and Management Using Resource Governor for memory management Memory usage report Metadata Enhancements and Additions Catalog view enhancements Dynamic Management Objects Extended events Performance counters Best Practices for Designing Memory-Optimized Tables and Indexes Best Practices for Managing Statistics on Memory-Optimized Tables Migration Considerations Common application bottlenecks that in-memory OLTP can resolve Application requirements compatible with migration to in-memory OLTP High volume of INSERTs High volume of SELECTs CPU-intensive operations Extremely fast business transactions Session state management Unsuitable application requirements Current applications The migration process Workload analysis and baselining Summary Additional Resources