as I know, OPTIMIZE FOR hint helps to avoid parameter sniffing.
For such a stored procedure:
SELECT * FROM Tabele WHERE param=@param1
We have two parameter
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 stored procedure, we have still parameter sniffing.
Now I have a conceptual question!:
How doe's OPTIMZE FOR hint help us actually to avoid parameter sniffing?