Query runs for days when upgrading to 2017

  • askcoffman

    Mr or Mrs. 500

    Points: 559

    We upgraded our datawarehouse sql server from 110 to 140, RTM - CU17. In the new version, the query in the link below was allowed to run for 24+ hours before killing it.

    https://www.brentozar.com/pastetheplan/?id=B1O4D08ar

    If I add option(use hint('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_110')), the query runs in 5 minutes.  This query is from our cube processing job so I can't embed the hint in the cube partition without getting syntax errors.

    A quick work around was to change the compatibility mode to 110 on the DB, process the cube, then revert back to 140.  Not ideal....

    I was able to spot on the execution plan that the period_dim is retrieving over 5 billion rows, but there are only 80K in the table.  Assuming this is the issue, how do I help sql get a better estimate?  All stats have been rebuilt using 100% sample.

  • Jeff Moden

    SSC Guru

    Points: 996832

    I don't recall the syntax off of the top of my head but there's some code to tell SQL Server to use the old cardinality estimator, which is what this problem seems to be all about.  I don't know for sure but I'd imagine they'll stop supporting that someday.  With that, if you know what code is causing you grief, I'd eventually fix it to work with the new cardinality estimator (CE).

    I didn't use such code when we ran into the problem because it affected so much of our code.  See Trace Flag 9481 at the following link for how to revert the whole box to the old CE, which is nasty overkill be we had to do it.  I recommend using the code I mentioned above for a much less broad stroke on this problem if you can isolate the problem quickly and to just a few things.

    https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

    Links to  the finer scoped code is available when you lookup Trace Flag 9481 above.

     

    --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)

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    Have you looked at the definitions of those two views?  You may be able to tweak the code so it works better with the "new" query optimizer.

    John

  • askcoffman

    Mr or Mrs. 500

    Points: 559

    The trace flag and the hint that I included do the same thing for the cardinality estimator.  I agree the real solution is to adjust the query to fit the 2017 CE.  For this purpose I included the execution plan hoping that someone who is more of an expert than me could read this and suggest a way to alter the query/view.  Would the the scripted view be helpful? I assumed including the execution plan would cover that.

  • John Mitchell-245523

    SSC Guru

    Points: 148769

    I think if anyone tried to reverse engineer the query from the execution plan, they'd lose the will to live!  Don't get me wrong - the execution plan is indeed useful, but we need to see the whole picture.  From my point of view, I'm just interested in the view definitions in case there's something really obvious that could easily be changed.

    John

  • Jeff Moden

    SSC Guru

    Points: 996832

    Just to add to what John has stated, I've found that a lot of the code that fails really needs a rewrite anyway.  If you can't fix a piece of code in an hour or so, consider scrapping it and starting anew.  Heh... and you know you're gonna add meaningful comments this time, as well, right? 😀

    --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)

  • askcoffman

    Mr or Mrs. 500

    Points: 559

    Thanks Jeff and John for the help.  I ended up rewriting the vw_pos_period_dim as a materialized view.  Still not sure why that worked since the view itself performed well, but when joined ran terribly.

  • Jeff Moden

    SSC Guru

    Points: 996832

    askcoffman wrote:

    Thanks Jeff and John for the help.  I ended up rewriting the vw_pos_period_dim as a materialized view.  Still not sure why that worked since the view itself performed well, but when joined ran terribly.

    A materialized view is basically a table behind the scenes.  It doesn't have to do the same calculations as your non-materialized view.

    --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)

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

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