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