Hi, Brent here! I’m really excited to welcome you to Mastering Query Tuning.
Read this whole page: there’s a lot of stuff in here for you.
Watch How to Think Like the Engine and Fundamentals of Index Tuning.
Before we get started, I need to make sure that we’ve covered the basics:
- Required: How to Think Like the Engine – free 90-minute class that covers the foundations of clustered and nonclustered indexes, statistics, memory grants, and sargability.
- Required: Fundamentals of Index Tuning – one-day class that covers how to index for WHERE, ORDER BY, and JOINs, and covers how to pick the field order for indexes.
- Required: Fundamentals of Query Tuning – one-day class that introduces cardinality estimation and how to improve it.
- Optional, but recommended: take Mastering Index Tuning. This class will assume you already know these topics. You’ll be able to keep up without it, but you’ll find that you can really absorb queries much better if you’ve already mastered indexing.
Armed with the material in those classes, you’ll be well-equipped to keep up in this Mastering class.
If you’re attending live, join the Slack channel.
To ask questions and talk to the other attendees, go to https://www.brentozar.com/slack/ to get an instant invite, then join the #BrentOzarUnlimited channel. This way attendees can help each other with their questions during the live labs.
Audio will come through your computer speakers, so you’ll want clear speakers or a set of headphones. (You don’t have to worry about a microphone or webcam – other attendees won’t see or hear you.)
Want to run the demos on your own machine?
If you didn’t buy a lab VM, or if you want to run the demos after the class finishes, read the scripts and databases setup.
- 0.2 How to Set Up Your Own Lab Server
- 1.1 How SQL Server Builds Query Plans
- 1.2 Common Tools to Improve Cardinality Estimation
- 1.3 Specialist Tools: CTEs, Temp Tables, and APPLY
- 1.4 Lab 1 Setup: Improving Estimates
- 1.5 Using sp_BlitzCache to Find the Right (Wrong) Queries
- 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 Using Batches to Do a Lot of Work Without Blocking
- 3.3 Avoiding Deadlocks
- 3.4 SQL Server 2017 & 2019’s New Robots
- 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: Parameter Sniffing
- Bonus: Parameter Sniffing Lab
- Bonus: Storytelling Time