You are previewing Inside the SQL Server Query Optimizer.
O'Reilly logo
Inside the SQL Server Query Optimizer

Book Description

The SQL Server Query Optimizer is perceived by many to be a magic black box, transforming SQL queries into high performance execution plans in the blink of an eye through some unknowable process. The truth is that, while the Query Optimizer is indeed the highly-complex result of decades of research, learning how it works its magic is not only possible, but immensely useful to database developers and administrators alike. A better understanding of what the Query Optimizer does behind the scenes can help you to improve the performance of your databases and applications, and this book explains the core concepts behind how the SQL Server Query Optimizer works. With this knowledge, you'll be able to write superior queries, provide the Query Optimizer with all the information it needs to produce efficient execution plans, and troubleshoot the cases when the Query Optimizer is not giving you the best plan possible. With over 15 years of experience in the use of Relational Databases (including SQL Server since version 6.5), Benjamin has watched the SQL Server Relational Engine grow and evolve. His insight will leave you with an excellent foundation in the practicalities of the Query Optimizer, and everything you need to know to start tuning your queries to perfection.

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgements
  5. Preface
  6. 1. Introduction to Query Optimization
    1. How the Query Optimizer Works
      1. Generating candidate execution plans
      2. Assessing the cost of each plan
      3. Query execution and plan caching
      4. Hinting
    2. Ongoing Query Optimizer Challenges
      1. A historical perspective
    3. Execution Plans
    4. Join Orders
    5. Summary
  7. 2. The Execution Engine
    1. Data Access Operators
      1. Scanning
      2. Seeking
      3. Bookmark lookup
    2. Aggregations
      1. Sorting and hashing
      2. Stream Aggregate
      3. Hash Aggregate
    3. Joins
      1. Nested Loops Join
      2. Merge Join
      3. Hash Join
    4. Parallelism
    5. Summary
  8. 3. Statistics and Cost Estimation
    1. Statistics
      1. Creating and updating statistics
      2. Inspecting statistics objects
      3. Density
    2. Histograms
    3. Statistics Maintenance
    4. Statistics on Computed Columns
    5. Filtered Statistics
    6. Cardinality Estimation Errors
    8. Cost Estimation
    9. Summary
  9. 4. Index Selection
    1. Introduction
    2. The Mechanics of Index Selection
    3. The Database Engine Tuning Advisor
    4. The Missing Indexes Feature
    5. Unused Indexes
    6. Summary
  10. 5. The Optimization Process
    1. Overview
    2. Peeking at the Query Optimizer
    3. Parsing and Binding
    4. Transformation Rules
    5. The Memo
    6. Optimization Phases
      1. Simplification
      2. Trivial plan
      3. Full optimization
        1. Search 0
        2. Search 1
        3. Search 2
    7. Summary
  11. 6. Additional Topics
    1. Updates
      1. Per-row and per-index plans
      2. Halloween protection
    2. Data Warehouses
    3. Parameter Sniffing
      1. Optimize for a typical parameter
      2. Optimize on every execution
      3. Local Variables and OPTIMIZE FOR UNKNOWN
    4. Auto-parameterization
    5. Forced parameterization
    6. Summary
  12. 7. Hints
    1. Before You Reach for a Hint...
    2. Types of Hints
      1. Joins
      2. Aggregations
      3. FORCE ORDER
      4. INDEX and FORCESEEK hints
      5. FAST N
    3. Plan Guides
    4. USE PLAN
    5. Summary