SQL Server Execution Plans for Developers and DBAs

Learn to make sense of what SQL Server is doing when it’s running your queries

Course Summary

One of the most important skills I provide when working at customer sites, is the ability to fix performance problems. And a key aspect of that is the ability to make sense of execution plans for the queries that are causing issues.

Have you worked with SQL Server or Azure SQL Database?
Do you think execution plans are important but wish you could make more sense of them?
Do you know the most important things to check for when you’re reading execution plans?
Are you wondering what spools are used for, or asserts, or wonder how parallelism, cursors, and query hints affect plans?
Do you think you know the basics but know it’s time to extend your knowledge by learning from an expert?

Look, if any of these apply to you, I hope you’ll join me because this course is for you. And as well as detailed instruction, the course has optional practical exercises and quizzes that will help you reinforce your learning.

Enroll now for $195 USD !

Modules and Lessons

Module 0: Getting started

Who is this course for ?
Who is Greg ?
What will I learn in this course ?
Configuring your lab environment
Configuring demo scripts and additional databases (optional)

Module 1: Core concepts

Module introduction
Why execution plans matter
SQL Server execution engines
Relational engine - parser
Relational engine - algebrizer
Relational engine - optimizer
Query cost
Core concepts quiz

Module 2: Execution plans

Module introduction
Query compilation
Estimated execution plans
Actual execution plans
Execution plan formats
Saving execution plans
Capture plans using SQL Trace or Profiler
Capture plans using Extended Events
Capture plans using DMVs
Reusing execution plans
Plan-affecting SET options
Execution plans quiz
Execution plans lab
Execution plans lab solution

Module 3: Reading execution plans

Module introduction
Operator types
Blocking operators
Node IDs and data flow
Data volumes
Operator cost
Operator properties
Reading execution plans quiz
Reading execution plans lab
Reading execution plans lab solution

Module 4: Basic operators

Module introduction
Select
Constant scan
Compute scalar
Table scan
Clustered index scan
Columnstore index scan
Clustered index seek
Index scan
Index seek
Top
Sort
Concatenation
Print
Basic operators quiz
Basic operators lab
Basic operators lab solution

Module 5: Lookups

Module introduction
Why lookups occur
RID lookup
Key lookup
Covering indexes
Lookups quiz
Lookups lab
Lookups lab solution

Module 6: Joins

Module introduction
Join strategy vs join type
SQL Server join types
Nested loop
Hash match
Merge join
Adaptive join
Bitmap filters
Joins quiz
Joins lab
Joins lab solution

Module 7: Aggregations

Module introduction
Aggregations
Hash match
Stream
Filter
Aggregations quiz
Aggregations lab
Aggregations lab solution

Module 8: Data modification

Module introduction
Insert
Update
Delete
Merge
Assert
Data modification quiz
Data modification lab
Data modification lab solution

Module 9: Views

Module introduction
Types of views
Standard views
Indexed views
Views quiz
Views lab
Views lab solution

Module 10: Spools and parallelism

Module introduction
Spools
Eager spools
Lazy spools
Table spools
Index spools
Row count spools
Window spools
Parallel queries
Spools and parallelism quiz
Spools and parallelism lab
Spools and parallelism lab solution

Module 11: Cursors

Module introduction
Cursor declarations
Forward-only read-only cursors
Static cursors
Keyset cursors
Dynamic cursors
Open cursor and fetch
Conditionals
Close and deallocate
Cursors quiz

Module 12: Advanced concepts

Module introduction
Compare plans
Live execution plans
Table hints
Join hints
Group and union hints
Compilation hints
Plan reuse hints
Query store
Plan guides
Forcing plans with query store
Advanced concepts quiz
Advanced concepts lab
Advanced concepts lab solution

Module 13: Next Steps

Summary and further steps

Enroll now for $195 USD !