Erik Darling points out that running sp_executesql with the WITH RECOMPILE setting doesn’t really change anything:
This’ll give us the key lookup plan you see above. If I re-run the query and use the 2010-12-30 date, we’ll re-use the key lookup plan.
That’s an example of how parameters are sniffed.
Sometimes, that’s not a good thing. Like, if I passed in 2008-12-30, we probably wouldn’t like a lookup too much.
One common “solution” to parameter sniffing is to tack a recompile hint somewhere.
Click through for Erik’s demonstration.