This lab is left as an extra exercise for the reader if you’d like to do it later on your own. So far in the class, we’ve been using 2017 compatibility mode. If you’d like to see how 2019 performs differently, you can turn on all of the cutting edge features on your version of…
Bonus Lab: Seeing the Effects of SQL Server 2019 Compat Mode
This lab is left as an extra exercise for the reader if you’d like to do it later on your own. So far in the class, we’ve been using 2017 compatibility mode. If you’d like to see how 2019 performs differently, you can turn on all of the cutting edge features on your version of...
To access this incredible, amazing content, you gotta get Mastering Parameter Sniffing – Live Classes + Instant Replay, 1 Year Access, Mastering Parameter Sniffing – Instant Replay Recordings Only, 1 Year Access or Recorded Class Season Pass, or log in if you already shelled out the cash.
- 0.1 Prerequisites Before the Class
- 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: Reducing Sniffing by Caching Multiple Plans
- 2.1 Spotting Wild Variations in the Plan Cache
- 2.2 How Freeing the Plan Cache Reduces the Blast Radius: usp_PlanCacheAutopilot
- 2.3 Tracking Even More Plan Cache Details with Query Store
- 2.4 Lab 3: Track Down Plans & Parameters
- 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 4 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
- 4.7 Tuning usp_RptPostLeaderboard
- 4.8 Tuning usp_RptAvgAnswerTimeByTag
- 4.9 Tuning usp_GetTagsForUser
- Bonus: Storytelling Time