Hi, Brent here! I’m really excited to welcome you to Fundamentals of Parameter Sniffing.
Read this whole page: there’s a lot of stuff in here for you.
To prep, watch two classes.
Before we get started, I need to make sure that we’ve covered the basics. In my free 90-minute How to Think Like the Engine class, you’ll learn the foundations of clustered and nonclustered indexes, statistics, memory grants, and sargability.
Then, complete the Fundamentals of Query Tuning class so that you’re comfortable with the basics of reading execution plans to find where cardinality estimation went wrong.
That alone doesn’t guarantee that you’ll be able to keep up in class, of course – but if you hadn’t at least learned this much before starting, then you’d have been totally screwed, hahaha.
Get your computer ready for the demos.
To follow along with the demos, download the 50GB Stack Overflow 2013 database. I’ll be using the medium-sized 50GB StackOverflow2013 database, and it’s vital that you use the same one. Parameter sniffing is all about getting different behavior based on your query’s parameters, so I need you to have the exact same data distribution that I’ll be working with onscreen.
Attach it to a:
- SQL Server 2017 or 2019 Developer Edition (Express Edition won’t work.) If you want a free development or evaluation version of SQL Server, hit up SQLServerUpdates.com.
- Use the default collation during install, SQL_Latin1_General_CP1_CI_AS (don’t get fancy with binary collations)
- The latest Cumulative Update
- The most recent SQL Server Management Studio (and it really does have to be the most recent one)
Install SQLQueryStress for load testing.
SQLQueryStress is an easy, free, open source load testing utility. Download the latest version (zip), and extract it to a folder. You don’t have to run a setup or anything – it’s just a single .exe file that you can run whenever you want to do a load test.
Then, download my SQLQueryStressLoadTests (below) and extract those to the same folder. Your folder will end up looking like this: