O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

SQL Server Execution Plans, 2nd Edition

Book Description

Every day the same types of questions come up repeatedly: Why is this query running slowly? Why is SQL Server ignoring my index? My response is the same in each case: have you looked at the execution plan? An execution plan describes what's going on behind the scenes when SQL Server executes a query. It shows how the query optimizer joined the data from the various tables defined in the query, which indexes it used, if any, how it performed any aggregations or sorting, and much more. It also estimates the cost of all of these operations, in terms of the relative load placed on the system. Every Database Administrator, developer, report writer, and anyone else who writes T-SQL to access SQL Server data, must understand how to read and interpret execution plans. My book leads you right from the basics of capturing plans, through how to interrupt them in their various forms, graphical or XML, and then how to use the information you find there to diagnose the most common causes of poor query performance, and so optimize your SQL queries, and improve your indexing strategy.

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Table of Contents
  5. About the Author
  6. Foreword
  7. Introduction
    1. Changes in this Second Edition
    2. Code Examples
  8. Chapter 1: Execution Plan Basics
    1. What Happens when a Query is Submitted?
      1. Query parsing
      2. Algebrizer
      3. The Query Optimizer
      4. Query Execution
    2. Estimated and Actual Execution Plans
    3. Execution Plan Reuse
    4. Clearing Plans from the Plan Cache
    5. Execution Plan Formats
      1. Graphical Plans
      2. Text Plans
      3. XML Plans
    6. Getting Started
      1. Permissions Required to View Execution Plans
      2. Working with Graphical Execution Plans
      3. Working with Text Execution Plans
      4. Working with XML Execution Plans
      5. Interpreting XML Plans
    7. Retrieving Plans from the Cache Using Dynamic Management Objects
    8. Automating Plan Capture Using SQL Server Trace Events
      1. Execution Plan Events
      2. Capturing a Showplan XML Trace
      3. Why the Actual and Estimated Execution Plans might Differ
    9. Summary
  9. Chapter 2: Graphical Execution Plans for Basic Queries
    1. The Language of Graphical Execution Plans
    2. Some Single Table Queries
      1. Clustered Index Scan
      2. Clustered Index Seek
      3. NonClustered Index Seek
      4. Key Lookup
      5. Table Scan
      6. Rid Lookup
    3. Table Joins
      1. Hash Match Join
      2. Nested Loops Join
      3. Compute Scalar
      4. Merge Join
    4. Filtering Data
    5. Execution Plans with Group by and Order by
      1. Sort
      2. Hash Match (Aggregate)
      3. Filter
      4. A Brief aside on Rebinds and Rewinds
    6. Execution Plans for Insert, Update and Delete Statements
      1. Insert Statements
      2. Update Statements
      3. Delete Statements
    7. Summary
  10. Chapter 3: Text and XML Execution Plans for Basic Queries
    1. Text Execution Plans
      1. A Text Plan for a Simple Query
      2. A Text Plan for a Slightly more Complex Query
    2. XML Execution Plans
      1. An Estimated XML Plan
      2. An Actual XML Plan
      3. Querying the XML
      4. Summary
  11. Chapter 4: Understanding more Complex Query Plans
    1. Stored Procedures
    2. Using a Sub-Select
    3. Derived Tables Using Apply
    4. Common Table Expressions
    5. Merge
    7. Indexes
    8. Summary
  12. Chapter 5: Controlling Execution Plans with Hints
    1. Query Hints
      1. Hash|Order Group
      2. Merge |Hash |Concat Union
      3. Loop|Merge|Hash Join
      4. Fast n
      5. Force Order
      6. Maxdop
      7. Optimize for
      8. Parameterization Simple|Forced
      9. Recompile
      10. Robust Plan
      11. Keep Plan
      12. Keepfixed Plan
      13. Expand Views
      14. Maxrecursion
      15. Use Plan
    2. Join Hints
      1. Loop
      2. Merge
    3. Table Hints
      1. Table Hint Syntax
      2. Noexpand
      3. Index()
      4. Fastfirstrow
    4. Summary
  13. Chapter 6: Cursor Operations
    1. Simple Cursors
      1. Logical Operators
      2. Physical Operators
    2. More Cursor Operations
      1. Static Cursor
      2. Keyset Cursor
      3. Read_Only Cursor
    3. Cursors and Performance
    4. Summary
  14. Chapter 7: Special Datatypes and Execution Plans
    1. XML
      1. For XML
      2. Openxml
      3. XQuery
    2. Hierarchical Data
    3. Spatial Data
    4. Summary
  15. Chapter 8: Advanced Topics
    1. Reading Large-Scale Execution Plans
    2. Parallelism in Execution Plans
      1. Max Degree of Parallelism
      2. Cost Threshold for Parallelism
      3. Are Parallel Plans Good or Bad?
      4. Examining a Parallel Execution Plan
    3. How Forced Parameterization Affects Execution Plans
    4. Using Plan Guides to Modify Execution Plans
      1. Object Plan Guides
      2. SQL Plan Guides
      3. Template Plan Guides
      4. Plan Guide Administration
      5. Plan Forcing
    5. Summary