Post reply

OPTIMIZE FOR UNKNOWN (parameter sniffing problem)

  • September 21, 2012 at 8:57 am

    #393230

    Hi,

    I recently read this post http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/ and not sure I got the "juice"...

    If I have a SP with 4 parameters to be applied on a SELECT statement do I have to "tell" SQL Server to "ignore" the values with the "OPTION (OPTIMIZE FOR (@p1 UNKNOWN, @p2 UNKNOWN, ...))".

    Is this the best way to optimize the query plan?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • September 21, 2012 at 9:02 am

    #1540772

    PiMané (9/21/2012)

    Is this the best way to optimize the query plan?

    No. It's a specific solution to a specific problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 21, 2012 at 9:04 am

    #1540775

    You can also use local variables for intermediaries. Assign your incoming parameters to local variables, then use the local variables in the predicates.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • September 21, 2012 at 9:10 am

    #1540784

    Roland Alexander STL (9/21/2012)

    You can also use local variables for intermediaries. Assign your incoming parameters to local variables, then use the local variables in the predicates.

    Yep, that one solution on the blog's post...

    My main question is if it's necessary for ALL SPs...

    Say you have an SP to list customers with 4 filters: @city, @sex, @maritalstatus and @anualincome

    There's a select on the SP that takes all 4 parameters...

    Do I have to tell SQL Server to OPTIMIZE FOR UNKOWN?

    @city can have multiple values so can @anualincome, but @sex and @maritalstatus have few values.

    Thanks,

    Pedro


    If you need to work better, try working less...

  • September 21, 2012 at 9:16 am

    #1540791

    As I said, no. It is a specific solution to a specific problem.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 21, 2012 at 9:19 am

    #1540793

    That depends on any number of factors. Parameter sniffing only presents a problem if there are multiple possible execution plans, some of which may be suboptimal for the parameter value passed. Consider a customer table with a state field, clustered on customer account number, with a nonclustered index on state. Assume ten thousand rows in the table. Eight thousand (80%) are from Texas. So if we have a stored procedure that selects based on state, the optimal plan involves scanning the clustered index, since this will be much more efficient than trying to do a key lookup. BUT there are only two rows from Vermont, and the select would benefit greatly from a key lookup for this parameter value. If the first plan compiled for the SP involved TX as a parameter, we will wind up using the same plan (CI scan) for Vermont as well as Texas, and this is suboptimal (obviously) for Vermont. Forcing the issue by using local variables will result in the optimizer generating a new plan that makes use of index statistics to generate the optimal plan for the value.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • September 21, 2012 at 9:31 am

    #1540804

    So it depends mostly on the data in the tables.

    Being a "generic" software spread across the country we can have databases with customers mainly from one state (where a cluster index scan would be better) and also databases witch customer from many different states (where a index seek would be better)..

    But since the query plan will be recompiled the 1st time on each database it will probably be optimized for its case.

    We can have a database where the plan says to use a index scan and another database with the same structure but different data with an index seek...

    There's not much that can be done in these situations since our SW is all over the country (20.000 customers) and each case is specific...

    Thanks,

    Pedro


    If you need to work better, try working less...

  • September 21, 2012 at 9:34 am

    #1540809

    PiMané (9/21/2012)

    So it depends mostly on the data in the tables.

    Being a "generic" software spread across the country we can have databases with customers mainly from one state (where a cluster index scan would be better) and also databases witch customer from many different states (where a index seek would be better)..

    But since the query plan will be recompiled the 1st time on each database it will probably be optimized for its case.

    We can have a database where the plan says to use a index scan and another database with the same structure but different data with an index seek...

    There's not much that can be done in these situations since our SW is all over the country (20.000 customers) and each case is specific...

    Thanks,

    Pedro

    Which is the point of using local variables, since it will force a new plan each time the query is run. Of course, this is also the downside of using local variables.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • September 21, 2012 at 9:39 am

    #1540813

    Roland Alexander STL (9/21/2012)

    Which is the point of using local variables, since it will force a new plan each time the query is run. Of course, this is also the downside of using local variables.

    Excuse my ignorance... but why are local variables a bad thing inside SP? They force the calculation of a new query plan (which is like recompiling?!)?

    Thanks,

    Pedro


    If you need to work better, try working less...

  • September 21, 2012 at 9:41 am

    #1540814

    Roland Alexander STL (9/21/2012)

    Which is the point of using local variables, since it will force a new plan each time the query is run.

    Errr... really?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 21, 2012 at 9:43 am

    #1540816

    PiMané (9/21/2012)

    Roland Alexander STL (9/21/2012)

    Which is the point of using local variables, since it will force a new plan each time the query is run. Of course, this is also the downside of using local variables.

    Excuse my ignorance... but why are local variables a bad thing inside SP? They force the calculation of a new query plan (which is like recompiling?!)?

    Thanks,

    Pedro

    Because forcing a new plan isn't ALWAYS a good thing. For example, if you have an SP that is retrieving a single row based on the PK, and that is being passed in as a parameter, there is no point in using a local variable and forcing a new plan because the plan will always be the same, no matter what value is passed to the SP. This is a case where local variables do not help, and actually hinder (due to the additional overhead of creating a new plan when the old one was just fine).

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • September 21, 2012 at 9:44 am

    #1540817

    GilaMonster (9/21/2012)

    Roland Alexander STL (9/21/2012)

    Which is the point of using local variables, since it will force a new plan each time the query is run.

    Errr... really?

    Oh, now, was that necessary? If I'm wrong, correct me: I'm always happy to be corrected.

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

  • September 21, 2012 at 9:48 am

    #1540820

    Just asking if you wanted to think about that some more... 😉

    Local variables in a procedure do not automatically result in recompiles of that plan. Plans that involve local variables are cached just like any other and the plans are reused just like any other. The main difference between using variables and not is that the optimiser can't sniff the value of variables (unless a statement recompiles) and hence cannot estimate the cardinality correctly. This can be helpful, in the cases of cardinality mis-estimates, but can also be harmful as it can cause those very mis-estimates.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 21, 2012 at 9:51 am

    #1540821

    Roland Alexander STL (9/21/2012)

    GilaMonster (9/21/2012)

    Roland Alexander STL (9/21/2012)

    Which is the point of using local variables, since it will force a new plan each time the query is run.

    Errr... really?

    Oh, now, was that necessary? If I'm wrong, correct me: I'm always happy to be corrected.

    Use of local variables doesn't force a new plan (Here I guess it's just a words usage problem, as you have mentioned in the next post that the plan can be exactly the same).

    However, it also doesn't guarantee recompilation.

    If you use local variables, SQL Server, will most likely come up with more generic plan and just use this one...

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • September 21, 2012 at 9:53 am

    #1540823

    GilaMonster (9/21/2012)

    Just asking if you wanted to think about that some more... 😉

    Which is a nice way of saying I may be typing faster than I'm thinking 😉

    The thought I was formulating was that going from non-local to local variables would force a new plan, not that the plan would be created anew each time the SP was run -> (face-palms self).

    Roland Alexander 
    The Monday Morning DBA 
    There are two means of refuge from the miseries of life: music and cats. ~ Albert Schweitzer

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply