Mastering Parameter Sniffing

Description

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 capture good, bad, and terrible plans with the plan cache and Query Store
• How to spot the common places where plans diverge: parallelism, memory grants, index selection, and join types
• How to prioritize which plan differences you need to correct, based on your server’s wait types, and which changes are harmless bystanders
• 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!

Prerequisites – first, you need to be already thoroughly familiar with these classes:

• How to Think Like the SQL Server Engine
• Fundamentals of Index Tuning
• Fundamentals of Query Tuning
• Fundamentals of Parameter Sniffing
• Optional: for the absolute best possible experience, we’d recommend taking Mastering Index Tuning before tackling this class. During Mastering Parameter Sniffing, we won’t be explaining how to design indexes when solving complex parameter sniffing issues with indexes – we’ll assume you already know how to do things like choose the right column orders and filters.
• After you enroll, do the full prerequisites including chat setup.

Each student needs to bring their own SQL Server. You’re highly encouraged to follow along with the labs – twice a day during the class, you’ll have homework where you need to complete a hands-on lab using SQL Server 2017 or 2019 and the Stack Overflow database. Read the instructions on how to set up your own lab SQL Server to follow along – a lab server is not included unless you buy my Live Class Season Pass PLUS Lab VM.

This mix of lectures and hands-on labs helps reinforce the topics you’re learning and makes sure that you’ll be able to tackle the same kinds of projects in your own environment. (Many students do just that: after hours, they switch into their own environments and use the same diagnostic queries to make immediate improvements in their apps.)

This is a live online class – but you have two buying choices:

• Instant Replay Recordings Only – you can start streaming the Instant Replay recordings and watch them for a year from your date of purchase.
• Live Class + Instant Replay – not only do you get instant access to the most recent recordings, but you can also attend all of the live deliveries of this class for 12 months after you buy your ticket. Read the live class logistics page. Here are the upcoming dates:
  • February 16-18, 2021 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal
  • May 5-7, 2021 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal