0.1 Prerequisites Before the Class
Hi, Brent here! I’m really excited to welcome you to the class.
Read this whole page: there’s a lot of stuff in here for you.
Watch How to Think Like the Engine and Fundamentals of Parameter Sniffing.
Before we get started, I need to make sure that we’ve covered the basics:
- How to Think Like the Engine – free 90-minute class that covers the foundations of clustered and nonclustered indexes, statistics, memory grants, and sargability.
- Fundamentals of Parameter Sniffing – 1-day class that covers what parameter sniffing is, and how to write queries that are susceptible to it.
Armed with the material in those two classes, you’ll be well-equipped to keep up in this Mastering class.
Now, get your computer ready for the webcast.
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.)
To follow along with the demos and do the homework assignments, here’s how to set up your training class VM. This actually takes a few hours, so set aside some time to do this long before class day.
- 0.2 Download the Slides and Scripts
- 1.1 How Index Tuning Reduces the Stench
- 1.2 How Query Hints Reduce the Stench
- 1.3 The Drawbacks of Using Recompile Hints
- 1.4 Lab 1: Get One Plan to Work Well Enough for Most
- 1.5 Bad Branching Causes Sniffing, Good Branching Reduces It
- 1.6 Lab 2: Fixing Parameter Sniffing Problems with Branching
- 2.1 Spotting Wild Variations in the Plan Cache
- 2.2 Trending the Plan Cache Over Time
- 2.3 Lab 3 Setup: Track Down Sniffing in the Plan Cache History
- 2.4 How Freeing the Plan Cache Reduces the Blast Radius: usp_PlanCacheAutopilot
- 2.5 Tracking Even More Plan Cache Details with Query Store
- 2.6 Lab 4 Setup: Track Down Sniffing Problems with Query Store and usp_PlanCacheAutopilot
- 3.1 How Memory Grant Feedback Multiplies Parameter Sniffing
- 3.2 How Adaptive Joins Help, and How to Get Them
- 3.3 How Automatic Tuning Mitigates Parameter Sniffing
- 4.1 Lab 5 Setup: Fixing the Problems You’ve Been Tracking Down
- 4.2 Tuning usp_SearchPostsByPostType
- 4.3 Tuning usp_RptFastestAnswers
- 4.4 Tuning usp_SearchPostsByLocation
- 4.5 Tuning usp_RptQuestionsAnsweredForUser
- 4.6 Tuning usp_SearchUsers
- Bonus Lab: Seeing the Effects of SQL Server 2019 Compat Mode
- Bonus: Storytelling Time