Fixing Plan Issues in the Middle of the Night

  • Jovan Popovic

    SSC Enthusiast

    Points: 160

    Comments posted to this topic are about the item Fixing Plan Issues in the Middle of the Night

  • Arsh

    SSCertifiable

    Points: 6251

    Hi Jovan,

    Want to know the effect of recompile option with SP execution in these versions considering the introduction of the mentioned query plan enhancements.

    Thanks...Arshad

  • Jeff Moden

    SSC Guru

    Points: 996810

    From the headline:


    Do you hate when someone wakes you up in the middle of the night and tells you that some query is slower and you need to investigate it?

    No.  I actually love it.  It means I get to kick someone's butt when I get into work because I will have already told them several times that it would be slow before they promoted it to production. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Arsh

    SSCertifiable

    Points: 6251

    Good one Jeff . I love it too ..and for the same reason 🙂

    .Arshad

  • Jeff Moden

    SSC Guru

    Points: 996810

    The problem with all this automatic stuff is that it may never actually do anything to help you identify the actual root of the problem and even the alleged better plan is a train-wreck to begin with.  For example, if you aren't updating statistics on large volumes of data changes, either the new or old plans could be absolutely horrible.  And there may be no good plan to be had because the ORM created a non-sargable bit of code that must recompile every time it's used (like we had) and it takes 2 to 22 seconds with an average of 7 seconds to compile each time.

    Yes, I agree that such automatic selection can help with occasional bad parameter sniffing but make sure this magical cure isn't giving you the nice-warm-fuzzies about things that you really should be proactively looking for and fixing instead.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Arsh

    SSCertifiable

    Points: 6251

    Jeff Moden - Tuesday, August 8, 2017 10:09 PM

    The problem with all this automatic stuff is that it may never actually do anything to help you identify the actual root of the problem and even the alleged better plan is a train-wreck to begin with.  For example, if you aren't updating statistics on large volumes of data changes, either the new or old plans could be absolutely horrible.  And there may be no good plan to be had because the ORM created a non-sargable bit of code that must recompile every time it's used (like we had) and it takes 2 to 22 seconds with an average of 7 seconds to compile each time.

    Yes, I agree that such automatic selection can help with occasional bad parameter sniffing but make sure this magical cure isn't giving you the nice-warm-fuzzies about things that you really should be proactively looking for and fixing instead.

    Absolutely true Jeff . Held similar views about statistics update on large volumes on a poorly designed fast increasing group of large tables (the largest is at 135 million rows increasing by over 1 million per day).Though there's a manual update every 24 hours stats become stale because of the volume of data change (not large enough for SQL server for auto update).

    Arshad

Viewing 6 posts - 1 through 6 (of 6 total)

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