Mastering Parameter Sniffing
You’ve conquered my Fundamentals of Parameter Sniffing class, and now it’s time to get to work. You know you have parameter sniffing problems in your environment – and now you need to learn how to fix ’em.
In this 3-day course, you’ll learn:
- How to spot the common places where plans diverge: parallelism, memory grants, index selection, and join types
- How to capture good, bad, and terrible plans with the plan cache and Query Store
- Ways to mitigate parameter sniffing with index changes, query tuning, and database-level settings
- How SQL Server 2017 and 2019 try (and usually fail) to reduce it with Adaptive Joins, Adaptive Memory Grants, and Automatic Tuning
This course is 100% demos: the only slides are the introductions at the start of the class, and the recap at the end of the class. The rest of the time, we’ll be working in SQL Server Management Studio. Roll up your sleeves and join me!
We’ll start with a round of introductions so you can meet your fellow students, and then do a quick reminder of the most important lessons from the Fundamentals of Parameter Sniffing class. One of the most important ones: never, ever test with local variables. Always use real stored procedures & dynamic SQL. Parameter sniffing only happens with parameters, not local variables.
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.)
- 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 Lab: Seeing the Effects of SQL Server 2019 Compat Mode
- Bonus: Storytelling Time