OPTION (OPTIMIZE FOR ...multiple values for a single parameter)

Transact-SQL
1

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.

Thanks,
Jeff

2

Hi,
You could have a main SP and inside it you can have two different procedures , these are call base on the values of your parameters.

The sniffing aspect is very wide , so this is much depending on your particularly code.

1 Like
3

What do you want to happen for the bad value?

If you optimize using a single good value, it will generate some plan, say Plan X. Should the bad value use Plan X? Or a different plan?

I guess I'm asking why you can't always optimize for a good value. If you want different plans based on different values, I'd do what @stepson suggested and create a wrapper for two different procedures.