During the quarantine downtime, I’m working on a couple of new classes that I think you’re gonna like. Both of them are 100% demos, no slides (other than introductions at the start of the day), complete with hands-on lab assignment challenges for you to solve. They’re both half off this month to celebrate the launch, too.

Fundamentals of Parameter Sniffing

Sometimes the exact same query goes slow out of nowhere.

Your current fix is to update statistics, rebuild indexes, or restart the SQL Server. It works, but you don’t know why.

You’re facing a problem called parameter sniffing: SQL Server “sniffs” the first set of parameters for a query, builds an execution plan for it, and then reuses that same plan no matter what parameters get called. It’s been the bane of our performance tuning for decades, and it doesn’t appear to be going away anytime soon – in fact, in newer versions of SQL Server, it even gets worse!

In this one-day live online class, you’ll follow along with me in the Stack Overflow database on your own laptop or desktop as you learn:

• What kinds of queries are vulnerable to parameter sniffing
• How to test your code to see if it’s vulnerable
• Why tactics like rebuilding indexes and updating statistics fix one query, but make others worse
• How to proactively patrol your server to uncover the problems before they bring you down

Upcoming class dates are May 26, August 4, November 16th, and more. You can register for this class now, plus Live Class Season Pass holders can attend this class free (here’s how), and Recorded Class Season Pass holders will get the recorded version of the class free after the first class delivery.

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 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

Upcoming class dates are June 3-5, August 5-7, and more. You can register for this class now, plus Live Class Season Pass holders can attend this class free (here’s how), and Recorded Class Season Pass holders will get the recorded version of the class free after the first class delivery.

Both classes are half off this month to celebrate the launch.

I’m so excited to share these with y’all because parameter sniffing is just such a fun topic. When I first sat down to write these courses, I thought I’d only have a day of material, tops – but the more I wrote, the more I realized I’m not even sure I can cover it all in four days, especially with the hands-on homework labs. Your first lab in Fundamentals requires you to write your own single-table query that’s going to be susceptible to parameter sniffing, and then prove it. It only gets more challenging from there, and I can’t wait to see how you do.