SQL Server – Using OPTIMIZE FOR Hints in Queries

hintssql serverstored-procedurest-sql

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?

Best Answer

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.

Related Question