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?
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.
