SQL Server – Query Execution Internals

Course Description

This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans – how they are created and how SQL Server uses them to execute a query.

Course Objectives

After successful completion of this course, students will be able to:

• generate graphical and XML execution plans
• access "post-compilation" execution plans and "post-execution" execution plans
• read and interpret execution plans
• analyze the control flow as well as the data flow of an execution plan
• identify estimated and actual values of
  • CPU cost
  • I/O cost
  • Row counts
  • Memory usage
• explain different data access patterns
• identify suboptimal data access operators
• recognize suboptimal Joins, Sorts and Aggregates
• explain insert / update operations
• recognize spool operators and their impacts on performance
• describe parallelism exchange operators
• recognize parallelism-preventing operations
• identify memory-consuming queries

Intended Audience

This intensive class is designed for SQL Server professionals that write or maintain T-SQL code.

Course Prerequisites

To get the most out of this class, students should have at least one year of solid working experience in writing queries and stored procedures for SQL Server.

Course Topics

Day One

1. Query Execution Phases
   • Parse
   • Bind
   • Optimize
   • Execute
   • Summary
2. Execution plans
   • RelOps Interface
   • RelOps Properties
   • Execution plan forms
   • Execution plan sources
   • Query cost
   • Runtime information
3. Data Access Operations
   • Indexes
   • Table Scan
   • Index Scan
   • Index Seek
   • Key Lookup
   • Constant Scan
4. Joins
   • Inner, outer, semi, apply
   • Nested Loops
   • Hash
   • Merge
   • Apply

Day Two

1. Calculations and Functions
   • Constants (Row & Columns)
   • Calculations
   • Function Calls
   • Filters
2. TOP
3. Sorts
   • Operators and Algorithms
   • TOP(n) Sort
   • Distinct
4. Unions
   • Hash
   • Concatenation
   • Merge Join (Concatenation)
5. Aggregates
6. Data Change Queries
7. Spools
   • Table Spool
   • Index Spool
   • Row Count Spool
8. Parallelism
9. Blocking and memory-consuming queries

How to attend

If you would like us to teach it in your neighborhood, contact us now.