Expert SQL Server In-Memory OLTP

Expert SQL Server In-Memory OLTP

Released September 2015
Publisher(s): Apress
ISBN: 9781484211366

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Expert SQL Server In-Memory OLTP is a deep dive into one of the most significant features of SQL Server 2014 – support for In-Memory Online Transaction Processing. The 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 transactional throughput to handle thousands of transactions per second supporting millions of customers.

Dmitri Korotkevitch is the five-star author of Pro SQL Server Internals, and now brings his same combination of clear thinking and deep expertise to the question of how to recognize the opportunities and benefit from Microsoft’s In-Memory OLTP feature set in SQL Server 2014.

Learn the architecture and the internals in order to recognize when in-memory OLTP can make a difference. Learn useful scenarios for thoughtfully incorporating In-Memory support into existing applications. Recognize opportunities for In-Memory OLTP in new development. Don’t be without Dmitri Korotkevitch and the deep expertise he imparts in Expert SQL Server In-Memory OLTP as you move forward in using SQL Server’s new and important In-Memory OLTP feature set.

  • Covers In-Memory OLTP internals and architecture, including data storage, indexing, multi-version concurrency control, transaction logging, and recovery
  • Illustrates In-Memory OLTP programmability and the process of native compilation
  • Guides in using In-Memory OLTP in new development and existing systems.
    •

    Table of contents

    1. Cover
    2. Title
    3. Copyright
    4. Dedication
    5. Contents at a Glance
    6. Contents
    7. About the Author
    8. About the Technical Reviewer
    9. Acknowledgments
    10. Introduction
    11. Chapter 1 : Why In-Memory OLTP?
      1. Background
      2. In-Memory OLTP Engine Architecture
      3. Summary
    12. Chapter 2 : In-Memory OLTP Objects
      1. Preparing a Database to Use In-Memory OLTP
      2. Creating Memory-Optimized Tables
      3. Working with Memory-Optimized Tables
      4. In-Memory OLTP in Action: Resolving Latch Contention
      5. Summary
    13. Chapter 3 : Memory-Optimized Tables
      1. On-Disk vs. Memory-Optimized Tables
      2. Introduction to the Multiversion Concurrency Control
      3. Data Row Format
      4. Native Compilation of Memory-Optimized Tables
      5. Memory-Optimized Tables: Surface Area and Limitations
        1. Supported Data Types
        2. Constraints and Table Features
        3. Database-Level Limitations
      6. High Availability Technologies Support
      7. Summary
    14. Chapter 4 : Hash Indexes
      1. Hashing Overview
      2. Much Ado About Bucket Count
        1. Bucket Count and Performance
        2. Choosing the Right Bucket Count
        3. Changing the Bucket Count in the Index
      3. Hash Indexes and SARGability
      4. Statistics on Memory-Optimized Tables
      5. Summary
    15. Chapter 5 : Nonclustered Indexes
      1. Working with Nonclustered Indexes
        1. Creating Nonclustered Indexes
        2. Using Nonclustered Indexes
      2. Nonclustered Indexes Internals
        1. Bw-Tree Overview
        2. Index Pages and Delta Records
      3. Obtaining Information About Nonclustered Indexes
      4. Hash Indexes vs. Nonclustered Indexes
      5. Summary
    16. Chapter 6 : In-Memory OLTP Programmability
      1. Native Compilation
        1. Natively Compiled Stored Procedures
        2. Creating Natively Compiled Stored Procedures
        3. Supported T-SQL Features
        4. Atomic Blocks
        5. Optimization of Natively Compiled Stored Procedures
      2. Interpreted T-SQL and Memory-Optimized Tables
      3. Performance Comparison
      4. Memory-Optimized Table Types and Variables
      5. Summary
    17. Chapter 7 : Transaction Processing in In-Memory OLTP
      1. ACID, Transaction Isolation Levels, and Concurrency Phenomena Overview
      2. Transaction Isolation Levels in In-Memory OLTP
      3. Cross-Container Transactions
      4. Transaction Lifetime
      5. Summary
    18. Chapter 8 : Data Storage, Logging, and Recovery
      1. Data Storage
        1. Checkpoint File Pairs States
      2. Transaction Logging
      3. Recovery
      4. Summary
    19. Chapter 9 : Garbage Collection
      1. Garbage Collection Process Overview
      2. Garbage Collection-Related Data Management Views
      3. Exploring the Garbage Collection Process
      4. Summary
    20. Chapter 10 : Deployment and Management
      1. Hardware Considerations
        1. CPU
        2. I/O Subsystem
        3. Memory
      2. Administration and Monitoring Tasks
        1. Limiting the Amount of Memory Available to In-Memory OLTP
        2. Monitoring Memory Usage for Memory-Optimized Tables
        3. Monitoring In-Memory OLTP Transactions
        4. Collecting Execution Statistics for Natively Compiled Stored Procedures
      3. Metadata Changes and Enhancements
        1. Catalog Views
        2. Data Management Views
        3. Extended Events and Performance Counters
      4. Summary
    21. Chapter 11 : Utilizing In-Memory OLTP
      1. Design Considerations for the Systems Utilizing In-Memory OLTP
      2. Addressing In-Memory OLTP Limitations
        1. 8,060-Byte Maximum Row Size Limit
        2. Lack of Uniqueness and Foreign Key Constraints
        3. Case-Sensitivity Binary Collation for Indexed Columns
      3. Thinking Outside the In-Memory Box
        1. Importing Batches of Rows from Client Applications
        2. Using Memory-Optimized Objects as Replacements for Temporary and Staging Tables
        3. Using In-Memory OLTP as Session - or Object State-Store
      4. Using In-Memory OLTP in Systems with Mixed Workloads
      5. Summary
    22. Appendix A: Memory Pointers Management
      1. Memory Pointers Management
      2. Summary
    23. Appendix B: Page Splitting and Page Merging in Nonclustered Indexes
      1. Nonclustered Indexes Internal Maintenance
        1. Page Splitting
        2. Page Merging
      2. Summary
    24. Appendix C: Analyzing the States of Checkpoint File Pairs
      1. Sys.db_dm_xtp_checkpoint_files View
      2. The Lifetime of Checkpoint File Pairs
      3. Summary
    25. Appendix D: In-Memory OLTP Migration Tools
      1. Management Data Warehouse Enhancements
      2. Memory Optimization and Native Compilation Advisors
      3. Summary
    26. Index

    Product information

    • Title: Expert SQL Server In-Memory OLTP
    • Author(s):
    • Release date: September 2015
    • Publisher(s): Apress
    • ISBN: 9781484211366