Parameter Sniffing - Hints and fixes

Question

I recently dealt with a problematic stored proc. At times, runs beautifully fast, other times, runs very very long. I determined that bad parameter sniffing was the cause.

Just for info - The parameters in the proc are datetime and the query uses these parameters to search through date ranges.

Anyway, this is what I attempted:

1. Recreated the proc and used WITH RECOMPILE - Didn't help
2. Recreated the proc and added OPTION (RECOMPILE) - Didn't help
3. Recreated the proc and added OPTION (OPTIMIZE FOR UNKNOWN) - Runs fast
4. Recreated the proc and used local variables - Runs fast

To help my understanding.... Is using local variables & OPTIMIZE FOR UNKNOWN the exact same thing in the way that is uses average density statistical data to produce a plan?

I tried a couple of combinations of things as well:

5. Recreated the proc and added OPTIMIZE FOR UNKNOWN & OPTION (RECOMPILE) - Runs fast
6. Recreated the proc with local variables & OPTION (RECOMPILE) - Runs slow

I have read about the potential dangers of using OPTIMIZE FOR UNKNOWN and in a lot of cases, using local variables are brought up as if it's the same thing. This is what leads me to think that it is the same thing.

BUT - How do I explain that attempt 6 runs slow.

I want to say that yes, stats are updated but it's with a less than zero % sampling rate - Tables are HUGE +- 1.6 billion rows.

Might also be worth noting - I used the awesome sp_blitzcache and filtered on the specific proc - There is a compilation timeout warning for it - My intuition is telling me that is something to note here.

Answer 1

Both OPTIMIZE FOR UNKNOWN and local variables (more on that in a second) use averages from the statistics to determine row counts. Parameter sniffing is going after specific values to get specific row counts, resulting in different plans as you're seeing. RECOMPILE is just forcing it to go after a different specific value. In some cases, this is the right answer. OPTIMIZE FOR a value also gets specific.

This is all very circumstance driven. Sometimes a generic plan based on averages will be better. Other times, one specific plan will be better (OPTIMIZE FOR ). Still other times, you can't & won't know what works best, so RECOMPILE becomes your friend (although it introduces other pain).

Now, I promised a little more on local variables. One thing to know is, at compile, the value will not be known, therefore, just like OPTIMIZE FOR UNKNOWN, you'll get averages. However, a statement level recompile will actually know what the value of the local variable is, and you can see bad plans generated if you're in the situation where you need that average value. So, generally, I wouldn't advise using the local variable and would instead stick with OPTIMIZE FOR UNKNOWN.

Also, Query Store and Plan Forcing is a great way to deal with bad parameter sniffing without mucking about with code. Just saying.