Post reply

Compatibility Mode causes query not to finish

  • August 8, 2015 at 5:33 am

    #319570

    Hi,

    We recently upgraded out SQL version from SQL2008R2 to SQL2014. As such, the compatibility mode changed to SQL2104 (120).

    We have several queries that used to run fine that now take forever to bring back results. There are no errors (which surprised me). They just take way too long now. PLus they seem to be causing high I/O and CPU.

    If I change the compt level back to SQL2008 - these queries run fine.

    QUERY with SQL2008 compt level - finished in 2 minutes.

    QUERY with SQL2014 compt level - finishes in 3 hours 22 minutes.

    same exact query - same server - only thing changed was compatibility level.

    WHat do I look for in the queries that could be causing this? (they look fine but obviously I'm missing something here)..

  • August 8, 2015 at 10:56 am

    #1818352

    My first question would be, did you rebuild the indexes and the statistics? Also, have you installed the good version of service pack 1?

    --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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 8, 2015 at 2:24 pm

    #1818369

    This sounds like you may have a couple of queries that fit into the edge cases where they do not work well under the SQL 2014 Cardinality Estimator. For those queries, you can use a trace flag to use the old CE. Most queries under the new CE will perform better, but some do far worse.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 8, 2015 at 2:47 pm

    #1818373

    -Is there a meterialized view that sits under the query. Might pay to rebuild the index

    (This might need to be done during low usage)

    -Have you got variable tables in there? If yes, also investigate the possibility of adding the 2453 trace flag for better row estimation.

  • August 8, 2015 at 11:25 pm

    #1818383

    Thanks Jeff forgot about SP1

  • August 8, 2015 at 11:26 pm

    #1818384

    You were correct Jason. Good call. Thanks.

  • August 8, 2015 at 11:35 pm

    #1818385

    Jeff Moden (8/8/2015)

    My first question would be, did you rebuild the indexes and the statistics? Also, have you installed the good version of service pack 1?

    Jeff - u may have nailed this one

    https://support.microsoft.com/en-us/kb/3044519

  • August 9, 2015 at 11:03 am

    #1818402

    NP. It IS one of the many reasons why I prefer to not be on the bleeding edge.

    --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".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • August 9, 2015 at 8:34 pm

    #1818420

    Jeff Moden (8/9/2015)

    NP. It IS one of the many reasons why I prefer to not be on the bleeding edge.

    Bleeding edge with major releases, SP, or even CU. Let it bake for a bit and then test it imho.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 10, 2015 at 6:50 am

    #1818518

    Several things....

    YES, rebuild indexes at bare minimum after moving from 2008 to 2014... any verison upgrade you should do that.

    Also, get the query and see what indexes it is or isn't using. If you see table scans in there that is bad. You might need new indexes. The optimizer in 2014 is drastically different and like a previous poster said 95% of things will run better and a few will be terrible from what I have read.

    Also, make sure stats are updated on at least a weekly basis if you don't have a scheduled task already doing this.

    Let us know what you find as all of us not on 2014 are curious for future upgrades.

    We have 4 new apps here on 2014 without any issues so far but I have not upgraded anything to 2014 yet.

Viewing 10 posts - 1 through 9 (of 9 total)

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