Performance Comparison Between SQL Server 2014 and 2016

  • savaramravindra4

    SSC Rookie

    Points: 34

    Comments posted to this topic are about the item Performance Comparison Between SQL Server 2014 and 2016

  • Jeff Moden

    SSC Guru

    Points: 996830

    In high contrast to what's in this good article, the change in the Cardinality Estimator for the upgrade from 2012 to 2016 crushed performance on several rather complicated queries and we had to use the TF (forget the number offhand) to cause the CE to revert back to the old version because we don't have a month to identify, repair, and regression test the repair.  The CE change also didn't help overall general performance.

    The "It just runs faster" claim MS made for 2016 just isn't true for us and actually made things worse.  To say the least, I'm more than mildly pissed at the whole thing.  Thank goodness they had the foresight (if that's what it actually was) to have a Trace Flag to revert the CE back to the old version.

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

  • rchantler

    SSCrazy

    Points: 2125

    We have run into problems with the CE in the past.  We are also about to upgrade from 2008R2 to 2016. Is the 2016 version of CE fixed by a patch or Service Pack?  Also, has the situation improved with 2017?

  • bpwilso

    SSC-Addicted

    Points: 448

    Definitely have to agree with Jeff there.  When we upgraded from the CE changes gave us a performance nightmare.  Having spent some time updating our code to work within the new CE we now have better performance or the same in most every case.  There are a few outliers where we still haven't been able to get performance back to where it was, but those areas are seldom used and not a priority.  It really was rough having to tell customers to utilize compatibility mode, or trying to justify use of a TraceFlag.

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

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