I have a parameter sniffing issue that I'm trying to minimize. Currently my stored procedure has the WITH RECOMPILE option set and it does an OK job at leveling the playing field across most of the arguments I pass it.
I've figured out that there is one condition (parameter) that results in a vastly different execution plan than the others. Is there a way to either specify multiple values for a single parameter in the OPTIMIZE FOR hint or maybe a way to specify all values except a specific value? I've tried OPTION (OPTIMIZE FOR @p1 <> 'myValue') but the SQL compiler complains that this isn't valid syntax.
There must be a way that I could filter out this one condition that results with a bad plan for 80% of the executions.
Any ideas? I can live with Option Recompile but if there was a way to avoid recompiling most of the time that would obviously be better.