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 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
In one day, I can’t teach you all of the ways to fix your code, indexes, and server – that’s where my Mastering Parameter Sniffing, Query Tuning, and Index Tuning classes come in. This is just the vital first step in your journey to understanding and solving parameter sniffing issues.
This course is 100% demos: the only slides are the introductions at the start of the day, and the recap at the end of the day. The rest of the time, we’ll be working in SQL Server Management Studio. Roll up your sleeves and join me!
- Before this class, watch the free How to Think Like the SQL Server Engine class and the Fundamentals of Query Tuning class.
- Have a local SQL Server 2017 or newer. Your server’s power doesn’t really matter – you can run this class’s labs on your local desktop just fine as long as you’ve got enough space for the database.
- Download the Stack Overflow 2013 database (medium, 50GB).
- Follow the class prerequisite instructions.
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 scheduled so far:
- May 14, 2021 online 9:00-5:00 Eastern (click to see in your own time zone) – iCal