Performance Comparison Between SQL Server 2014 and 2016

Question

Performance Comparison Between SQL Server 2014 and 2016

savaramravindra4

SSC Rookie

Points: 34
More actions
February 21, 2018 at 11:03 pm

#336197

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

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

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

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.

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

rchantler SSCrazy Points: 2128 More actions · Answer 2

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 More actions · Answer 3

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.