Pro SQL Server Internals

Pro SQL Server Internals

by Dmitri Korotkevitch
Released June 2014
Publisher(s): Apress
ISBN: 9781430259626

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

Pro SQL Server Internals explains how different SQL Server components work "under the hood" and how they communicate with each other. This is the practical book with a large number of examples that will show you how various design and implementation decisions affect the behavior and performance of your systems.

Pro SQL Server Internals covers a multiple SQL Server versions starting with SQL Server 2005 all the way up to the recently released SQL Server 2014. You’ll learn about new SQL Server 2014 features including the new Cardinality Estimator, In-Memory OLTP Engine (codename Hekaton), and Clustered Columnstore Indexes. With Pro SQL Server Internals, you have a solid roadmap for understanding the depth and power of the SQL Server database backend, regardless of the version and edition of SQL Server you use.

Pro SQL Server Internals does the following:

  • Explains how to design efficient database schema, indexing, and transaction strategies.
  • Shows how various database objects and technologies are implemented internally and when they should or should not be used.
  • Demonstrates how SQL Server executes queries and works with data and transaction logs.

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 Reviewers
  9. Acknowledgments
  10. Introduction
  11. Part 1: Tables and Indexes
    1. Chapter 1: Data Storage Internals
      1. Database Files and Filegroups
      2. Data Pages and Data Rows
      3. Large Objects Storage
        1. Row-Overflow Storage
        2. LOB Storage
      4. SELECT * and I/O
      5. Extents and Allocation Map Pages
      6. Data Modifications
      7. Much Ado About Data Row Size
      8. Table Alteration
      9. Summary
    2. Chapter 2: Tables and Indexes: Internal Structure and Access Methods
      1. Heap Tables
      2. Clustered Indexes
      3. Composite Indexes
      4. Nonclustered Indexes
      5. Summary
    3. Chapter 3: Statistics
      1. Introduction to SQL Server Statistics
      2. Column-Level Statistics
      3. Statistics and Execution Plans
      4. Statistics and Query Memory Grants
      5. Statistics Maintenance
      6. SQL Server 2014 Cardinality Estimator
        1. Comparing Cardinality Estimators: Up-to-Date Statistics
        2. Comparing Cardinality Estimators: Outdated Statistics
        3. Comparing Cardinality Estimators: Indexes with Ever-Increasing Key Values
        4. Comparing Cardinality Estimators: Joins
        5. Comparing Cardinality Estimators: Multiple Predicates
      7. Summary
    4. Chapter 4: Special Indexing and Storage Features
      1. Indexes with Included Columns
      2. Filtered Indexes
      3. Filtered Statistics
      4. Calculated Columns
      5. Data Compression
        1. Row Compression
        2. Page Compression
        3. Performance Considerations
      6. Sparse Columns
      7. Summary
    5. Chapter 5: Index Fragmentation
      1. Types of Fragmentation
      2. FILLFACTOR and PAD_INDEX
      3. Index Maintenance
      4. Designing an Index Maintenance Strategy
      5. Patterns that Increase Fragmentation
      6. Summary
    6. Chapter 6: Designing and Tuning the Indexes
      1. Clustered Index Design Considerations
        1. Design Guidelines
        2. Identities, Sequences, and Uniqueidentifiers
      2. Nonclustered Indexes Design Considerations
      3. Optimizing and Tuning Indexes
        1. Detecting Unused and Inefficient Indexes
        2. Index Consolidation
        3. Detecting Suboptimal Queries
      4. Summary
  12. Part 2: Other things that matter
    1. Chapter 7: Constraints
      1. Primary Key Constraints
      2. Unique Constraints
      3. Foreign Key Constraints
      4. Check Constraints
      5. Wrapping Up
    2. Chapter 8: Triggers
      1. DML Triggers
      2. DDL Triggers
      3. Logon Triggers
      4. UPDATE( ) and COLUMNS_UPDATED( )functions
      5. Nested and Recursive Triggers
      6. First and Last Triggers
      7. CONTEXT_INFO
      8. Summary
    3. Chapter 9: Views
      1. Regular Views
      2. Indexed Views
      3. Partitioned Views
      4. Updatable Views
      5. Summary
    4. Chapter 10: User-Defined Functions
      1. Multi-Statement Functions
      2. Inline Table-Valued Functions
      3. Summary
    5. Chapter 11: XML
      1. To Use or Not to Use XML? That Is the Question!
      2. XML Data Type
      3. Working with XML Data
        1. value() Method
        2. exists() Method
        3. query() Method
        4. nodes() Method
        5. modify() Method
      4. OPENXML
      5. FOR XML
      6. Summary
    6. Chapter 12: Temporary Tables
      1. Temporary Tables
      2. Table Variables
      3. User-Defined Table Types and Table-Valued Parameters
      4. Regular Tables in tempdb
      5. Optimizing tempdb Performance
      6. Summary
    7. Chapter 13: CLR
      1. CLR Integration Overview
      2. Security Considerations
      3. Performance Considerations
      4. Summary
    8. Chapter 14: CLR Types
      1. User-Defined CLR Types
      2. Spatial Data Types
      3. HierarchyId
      4. Summary
    9. Chapter 15: Data Partitioning
      1. Reasons to Partition Data
      2. When to Partition?
      3. Data Partitioning Techniques
        1. Partitioned Tables
        2. Partitioned Views
        3. Comparing Partitioned Tables and Partitioned Views
        4. Using Partitioned Tables and Views Together
      4. Tiered Storage
        1. Moving Non-Partitioned Tables Between Filegroups
        2. Moving Partitions Between Filegroups
        3. Moving Data Files Between Disk Arrays
        4. Tiered Storage in Action
        5. Tiered Storage and High Availability Technologies
      5. Implementing Sliding Window Scenario and Data Purge
      6. Potential Issues
      7. Summary
    10. Chapter 16: System Design Considerations
      1. General System Architecture
      2. Data Access Layer Design
        1. Connection Pooling
        2. Working with Database Tables Directly
        3. Database Views
        4. Stored Procedures
        5. Code Generators and ORM Frameworks
      3. Analyzing Microsoft Entity Framework 6
        1. Connections and Transactions
        2. Executing Stored Procedures and Queries
        3. Data Loading
        4. Parameterization
        5. IN Lists
        6. Deletions
        7. Optimistic Concurrency
        8. Conclusions
      4. Summary
  13. Part 3: Locking, Blocking and Concurrency
    1. Chapter 17: Lock Types
    2. Chapter 18: Troubleshooting Blocking Issues
      1. General Troubleshooting Approach
      2. Troubleshooting Blocking Issues in Real Time
      3. Collecting Blocking Information for Further Analysis
      4. Summary
    3. Chapter 19: Deadlocks
      1. Classic Deadlock
      2. Deadlock Due to Non-Optimized Queries
      3. Deadlock Due to Simultaneous Read/Update Statements
      4. Deadlock Due to Multiple Updates of the Same Row
      5. Deadlock Troubleshooting
      6. Reducing the Chance of Deadlocks
      7. Summary
    4. Chapter 20: Lock Escalation
      1. Lock Escalation Overview
      2. Lock Escalation Troubleshooting
      3. Summary
    5. Chapter 21: Optimistic Isolation Levels
      1. Row Versioning Overview
      2. Optimistic Transaction Isolation Levels
        1. READ COMMITTED SNAPSHOT Isolation Level
        2. SNAPSHOT Isolation Level
      3. Version Store Behavior
      4. Summary
    6. Chapter 22: Application Locks
    7. Chapter 23: Schema Locks
      1. Schema Modification Locks
      2. Multiple Sessions and Lock Compatibility
      3. Lock Partitioning
      4. Low-Priority Locks (SQL Server 2014)
      5. Summary
    8. Chapter 24: Designing Transaction Strategies
  14. Part 4: Query Life Cycle
    1. Chapter 25: Query Optimization and Execution
      1. Query Life Cycle
      2. Query Optimization
      3. Query Execution
      4. Operators
        1. Joins
        2. Aggregates
        3. Spools
        4. Parallelism
      5. Query and Table Hints
        1. INDEX Query Hint
        2. FORCE ORDER Hint
        3. LOOP, MERGE, and HASH JOIN Hints
        4. FORCESEEK/FORCESCAN Hints
        5. NOEXPAND/EXPAND VIEWS Hints
        6. FAST N Hints
      6. Summary
    2. Chapter 26: Plan Caching
      1. Plan Caching Overview
      2. Parameter Sniffing
      3. Plan Reuse
      4. Plan Caching for Ad-Hoc Queries
      5. Auto-Parameterization
      6. Plan Guides
      7. Plan Cache Internals
      8. Examining Plan Cache
      9. Summary
  15. Part 5: Practical Troubleshooting
    1. Chapter 27: System Troubleshooting
      1. Looking at the Big Picture
        1. Hardware and Network
        2. Operating System Configuration
        3. SQL Server Configuration
        4. Database Options
      2. SQL Server Execution Model
      3. Wait Statistics Analysis and Troubleshooting
        1. I/O Subsystem and Non-Optimized Queries
        2. Memory-Related Wait Types
        3. High CPU Load
        4. Parallelism
        5. Locking and Blocking
        6. Worker Thread Starvation
        7. ASYNC_NETWORK_IO Waits
        8. Allocation Map Contention and Tempdb load
        9. Wrapping Up
      4. What to Do When the Server Is Not Responding
      5. Working with Baseline
      6. Summary
    2. Chapter 28: Extended Events
      1. Extended Events Overview
      2. Extended Events Objects
        1. Packages
        2. Events
        3. Predicates
        4. Actions
        5. Types and Maps
        6. Targets
      3. Creating Events Sessions
      4. Working with Event Data
        1. Working with the ring_buffer Target
        2. Working with event_file and asynchronous_file_target Targets
        3. Working with event_counter and synchronous_event_counter Targets
        4. Working with histogram, synchronous_bucketizer, and asynchronous_bucketizer Targets
        5. Working with the pair_matching Target
      5. System_health and AlwaysOn_Health Sessions
      6. Detecting Expensive Queries
      7. Summary
  16. Part 6: Inside the transaction log
    1. Chapter 29: Transaction Log Internals
      1. Data Modifications, Logging, and Recovery
      2. Delayed Durability (SQL Server 2014)
      3. Virtual Log Files
      4. Database Recovery Models
      5. TempDB Logging
      6. Excessive Transaction Log Growth
      7. Transaction Log Management
      8. Summary
    2. Chapter 30: Designing a Backup Strategy
      1. Database Backup Types
      2. Backing Up the Database
      3. Restoring the Database
        1. Restore to a Point in Time
        2. Restore with StandBy
      4. Designing a Backup Strategy
      5. Partial Database Availability and Piecemeal Restore
      6. Partial Database Backup
      7. Backup to Windows Azure
      8. Managed Backup to Windows Azure
      9. Summary
    3. Chapter 31: Designing a High Availability Strategy
      1. SQL Server Failover Cluster
      2. Database Mirroring
      3. AlwaysOn Availability Groups
      4. Log Shipping
      5. Replication
      6. Designing a High Availability Strategy
      7. Summary
  17. Part 7: In-Memory OLTP Engine
    1. Chapter 32: In-Memory OLTP Internals
      1. Why Hekaton?
      2. In-Memory OLTP Engine Architecture and Data Structures
        1. Memory-Optimized Tables
        2. High-Availability Technology Support
        3. Data Row Structure
        4. Hash Indexes
        5. Range Indexes
        6. Statistics on Memory-Optimized Tables
        7. Garbage Collection
      3. Transactions and Concurrency
        1. Transaction Isolation Levels and Data Consistency
        2. In-Memory OLTP Transaction Isolation Levels
        3. Cross-Container Transactions
        4. Data Access, Modifications, and Transaction Lifetime
        5. Transaction Logging
      4. Data Durability and Recovery
      5. Memory Usage Considerations
      6. Summary
  18. Part 8: Columnstore Indexes
    1. Chapter 33: In-Memory OLTP Programmability
      1. Native Compilation
      2. Natively-Compiled Stored Procedures
        1. Optimization of Natively-Compiled Stored Procedures
        2. Creating Natively-Compiled Stored Procedures
        3. Supported T-SQL Features
        4. Execution Statistics
      3. Interpreted T-SQL and Memory-Optimized Tables
      4. Memory-Optimized Table Types and Variables
      5. In-Memory OLTP: Implementation Considerations
      6. Summary
    2. Chapter 34: Introduction to Columnstore Indexes
      1. Data Warehouse Systems Overview
      2. Columnstore Indexes and Batch-Mode Processing Overview
        1. Column-Based Storage and Batch-Mode Execution
        2. Columnstore Indexes and Batch-Mode Execution in Action
      3. Columnstore Index Internals
        1. Nonclustered Columnstore Indexes
        2. Data Storage
        3. Metadata
      4. Design Considerations and Best Practices for Columnstore Indexes
        1. Reducing Data Row Size
        2. Giving SQL Server as Much Information as Possible
        3. Maintaining Statistics
        4. Avoiding String Columns in Fact Tables
      5. Summary
    3. Chapter 35: Clustered Columnstore Indexes
      1. Internal Structure of Clustered Columnstore Indexes
        1. Internal Structure
        2. Data Load
        3. Delta Store and Delete Bitmap
        4. Archival Compression
      2. Clustered Columnstore Index Maintenance
        1. Excessive Number of Partially Populated Row Groups
        2. Large Delta Stores
        3. Large Delete Bitmap
        4. Index Maintenance Options
      3. SQL Server 2014 Batch-Mode Execution Enhancements
      4. Design Considerations
      5. Summary
  19. Index

Product information

  • Title: Pro SQL Server Internals
  • Author(s): Dmitri Korotkevitch
  • Release date: June 2014
  • Publisher(s): Apress
  • ISBN: 9781430259626