0

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?

3

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.

| improve this answer | |
  • Thank you for answer, then using of OPTIMIZE FOR makes actually no big difference on parameter sniffing, but OPTIMIZE FOR UNKNOWN could make the performance of such a query better. I think there is no need for OPTIMIZE FOR – Kaja Apr 28 '15 at 8:05
  • 1
    @Kaja Please benchmark the queries. A careful examination of execution plans will tell you if incorrect sniffing happens or not. – vonPryz Apr 28 '15 at 8:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.