Mastering Query Tuning
You need to speed up a SQL Server app, and you’re allowed to change the queries and indexes – but not the server hardware or settings. Good news – I’ll teach you how in 3 days of learning and fun. Join me, Brent Ozar, as I explain how to make your SQL Server apps go faster.
Labs & lectures include:
- How SQL Server builds query plans
- How to find the queries you need to focus on first so end users will notice an improvement
- How to choose between CTEs, temp tables, and APPLY
- How to tune for SELECT * and lots of rows
- How to write dynamic SQL that scales
- How to avoid pitfalls like deadlocks and bad batching
- How SQL Server 2017 & 2019’s new robots help (and hurt)
Your Progress So Far in This Class
This is driven by the mark-as-complete buttons in each module of the class. (Let’s be honest: you’re probably just going to mark them as complete because you’re that kind of student. I feel you.)
Recent Updates & Changes
2025-11-16:
- The Architect and the Builder – updated and expanded all of these modules using SQL Server 2025.
- Dynamic SQL – updated, expanded, and broke into two modules to elaborate on more stuff with SQL Server 2025.
- Avoiding Deadlocks by Modifying T-SQL – added a new video to demo SQL Server 2025’s new Optimized Locking.
- Lab setup intro videos – re-recorded with logistical updates, but no need for you to re-watch those if you’ve already done the labs, and the labs themselves didn’t change.
2025-11-12:
- Tuning for Select Star and Lots of Rows – re-recorded with SQL Server 2025 just to show & explain that 2025’s PSPO and OPPO don’t fix the problem we’re addressing.
Introductions
Click on the modules on the right side of the page to keep moving through the class.
- 0.1 Prerequisites Before the Class (59 min)
- 0.2 Download the Slides and Scripts
- 1.1 How SQL Server Builds Query Plans
- 1.2 When the Architect Gets an Early Estimate Wrong
- 1.3 When the Architect Gets a Late Estimate Wrong
- 1.4 Lab 1 Setup: Improving Estimates
- 1.5 The Tuning Robots in SQL Server 2017, 2019, and 2022
- 1.6 Lab 2 Setup: Analyzing a Running Workload
- 2.1 Tuning for SELECT * and Lots of Rows
- 2.2 User-Defined Functions
- 2.3 Lab 3 Setup: Changing Results and Parameters
- 2.4 Dynamic SQL Pro Tips
- 2.5 Lab 4 Setup: Advanced Rewrites
- 3.1 How Parallelism Balances Work Across Threads
- 3.2 Avoiding Deadlocks
- 3.3 Using Batches to Do a Lot of Work Without Blocking
- 4.1 Lab 5 Setup: The Final Lab
- 4.2 Final Lab: Index Tuning
- 4.3a Final Lab: usp_Q1080
- 4.3a Final Lab: usp_Q6627
- 4.3a Final Lab: usp_Q8116
- 4.3a Final Lab: usp_Report3
- 4.3b Final Lab: Logging sp_BlitzCache to a Table
- 4.3c Final Lab: usp_Q7521
- 4.3e Final Lab: usp_FindRelatedPosts
- 4.3f Final Lab: usp_Q6627, Take 2
- Bonus: Storytelling Time
- Bonus: The Ascending Key Problem