Using of OPTIMIZE FOR hints in a sql query

1 vote

1 answer

904 views

sql-server stored-procedures hints parameter-sniffing

                          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?

Asked by Kaja (143 rep)

Apr 28, 2015, 07:17 AM
Last activity: Mar 31, 2023, 05:15 AM

Using of OPTIMIZE FOR hints in a sql query

Related Questions