Normally when we think about parameter sniffing, we think about a tiny data vs big data problem: the tiny data’s plan goes in first, and when we try to process big data, performance is terrible.
But sometimes, it’s the exact opposite.
I’ll start with the large Stack Overflow database (I’m using the 2018-06 version, but any large one will do) and write a stored procedure to find the most recent posts authored by folks in a specific location:
1234567 | CREATE OR ALTER PROC dbo.RecentPostsByLocation @Location NVARCHAR(100) AS SELECT TOP 200 p.Title, p.Id, p.CreationDate FROM dbo.Posts p INNER JOIN dbo.Users u ON p.OwnerUserId = u.Id WHERE u.Location = @Location ORDER BY p.CreationDate DESCGO |
To help SQL Server out – I’m a giver like that – I’ll create a few indexes to give SQL Server some choices:
12345678 | USE StackOverflow;GODropIndexes;GOCREATE INDEX Location ON dbo.Users(Location);CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);CREATE INDEX CreatioDate ON dbo.Posts(CreationDate);GO |
Call it for tiny data first…
When we call the proc for a less-populated location first, SQL Server chooses to look up the people who live in Iceland (because there aren’t many), and then go find their posts. Small data finishes in under 1 second:
If we now run it for a larger location, like Germany, the query takes 24 seconds to run because we find so many people in Germany, and they’ve posted so many answers. (I’m sure they’re not asking a lot of questions. Germans are really smart.)
And the sort spills to disk because we only granted Iceland-size memory.
This is the typical parameter sniffing problem that people blog about: put the tiny data parameters in memory first, and SQL Server just isn’t equipped to deal with big data.
But if the big data plan goes in first…
Do things perform better? Let’s free the plan cache, then start with Germany:
This time around, SQL Server says, “Ah, Germany’s really common. You’re asking for these posts to be sorted by CreationDate – as a reminder, here’s your query and your indexes:”
123456789101112 | CREATE OR ALTER PROC dbo.RecentPostsByLocation @Location NVARCHAR(100) AS SELECT TOP 200 p.Title, p.Id, p.CreationDate FROM dbo.Posts p INNER JOIN dbo.Users u ON p.OwnerUserId = u.Id WHERE u.Location = @Location ORDER BY p.CreationDate DESCGO CREATE INDEX Location ON dbo.Users(Location);CREATE INDEX OwnerUserId ON dbo.Posts(OwnerUserId);CREATE INDEX CreatioDate ON dbo.Posts(CreationDate);GO |
“So since you’re asking for the top 200 by CreationDate descending, I’ll just scan the CreationDate index from newest to oldest. For each post I find, I’ll go look up the Users table to see where that user’s from. I bet I won’t have to look up too many before I’ll find 200 of ’em that were posted by Germans.”
And he’s right: he only had to look up about 20,000 of them before he found 200 that were written by Germans. Very cool.
But…when we run this for Iceland…
SQL Server has to read over a million Posts, doing over a million Posts key lookups, and over a million checks into the Users table before it’s found 200 posts that were written by Icelanders. Ten seconds isn’t so bad, but if you pass in an even rarer location, like the charming and easy-to-pronounce Hafnarfjordur, Iceland:
SQL Server again scans the index on Posts, doing a key lookup on Posts for every single row that it finds, and I bet that sounds goofy. Oh it gets worse: for all 40,700,647 Posts, it does a clustered index seek against the Users table. We do over 124 MILLION logical reads on a table that only has 143K pages in it. We read the Users table 871 times over, and the Posts table 6 times over due to all the key lookups:
That sure is a lot of work – would be nice if SQL Server parallelized that query across multiple threads. No can do, though – the query plan was designed with Germany in mind, when SQL Server thought it’d find rows quickly. The whole time this query runs, it’s just hammering one of my CPU cores while the rest sit idle:
There is no one good plan here.
I always laugh when I see demos that talk about “the good plan” and “the bad plan.” I wish the real world was so simple, so black-and-white. In reality, queries have many possible plans, and many of ’em just don’t work for other sets of parameters.
SQL Server 2017’s “Automatic Tuning” does nothing here, either: it can try throwing a different plan in thinking there’s been a regression, but it’s not smart enough to pick different execution plans for different parameters. It picks one plan and tries to make it work for all of ’em – and that simply doesn’t work here.