Using of OPTIMIZE FOR hints in a sql query

Question

As far as I know, the OPTIMIZE FOR hint helps to avoid parameter sniffing.

For such a stored procedure:

SELECT * FROM Table WHERE param=@param1

We have two parameters S1 with high selectivity and S2 with low selectivity.

If we are using such a hint:

OPTION(OPTIMIZE FOR @param1=S1)

and then send S2 to the stored procedure, we have still parameter sniffing.

Now I have a conceptual question:

How does the OPTIMZE FOR hint help us avoid parameter sniffing?

Answer 1

OPTIMIZE FOR is used for making a good plan for specific query. A classical example is a report to skewed data that is run very often with same parameters. In such a scenario, it could be useful to optimize the query for the most common parameter. This is a trade-off, as other queries with different parameter are going to get worse a plan.

If you are suffering from parameter sniffing, you could use OPTIMIZE FOR UNKNOWN, OPTION RECOMPILE or local variables. None of these is a silver bullet, so bencmark the queries carefully. Make sure the issue really is parameter sniffing and not, say, out-of-date statistics.

A Microsoft blog discusses the issue with sample code, as a question right here on dba.so.