July 27, 2020 at 10:33 pm
We are upgrading from SQL 2014 to SQL 2017. Noticed that integrity checks are running 6 times faster than SQL 2014. Is that a big enhancement in SQL 2017? Do you agree with me?
July 28, 2020 at 11:05 am
Hi,
it depends, did you an inplace upgrade?
What else has changed during upgrade?
Kind regards,
Andreas
July 28, 2020 at 12:07 pm
Tons of stuff is faster in 2017. You should see what happened with 2019. They improved all sorts of stuff even more. It's honestly insane just how much better the tool has become in just six years.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 28, 2020 at 1:37 pm
We are upgrading from SQL 2014 to SQL 2017. Noticed that integrity checks are running 6 times faster than SQL 2014. Is that a big enhancement in SQL 2017? Do you agree with me?
Rather than rely on opinionated answers for such opinionated questions, search for and read "What's new in SQL Server 2017". The answer is probably there.
The reason why I suggest this is because it's like asking if rCTE's seem faster in 2017. The answer might be "yes" but it's still the wrong thing to do if you're using incremental rCTEs.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2020 at 4:20 pm
I did run some tests. On SQL 2014 it is taking approx 8 - 10 hrs and on SQL 2017 it is taking less than 2 hrs. I can't believe the run time is much faster. Have anyone notice that much faster? Again it's with the same server configuration.Only difference is SQL 2014 has mirroring and SQL 2017 has always on. But that would not matter I guess for integrity check.
July 30, 2020 at 5:04 pm
I did run some tests. On SQL 2014 it is taking approx 8 - 10 hrs and on SQL 2017 it is taking less than 2 hrs. I can't believe the run time is much faster. Have anyone notice that much faster? Again it's with the same server configuration.Only difference is SQL 2014 has mirroring and SQL 2017 has always on. But that would not matter I guess for integrity check.
I have to admit, that's a surprise to me. They did advertise 2016 as "It just runs faster". We only saw a 2x increase in performance (and only on a handful of long runs) when we went from 2005 to 2012 (on all new hardware with SSDs etc) and no increase when we went from 2012 to 2016. At prior companies, most hardware and migrations produced some hopeful increases but that quickly faded as data scaled up. In a lot of cases, performance went down, sometimes dramatically like when they came out with the new Cardinality Estimator, which we almost immediately had to disable server wide.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 30, 2020 at 7:37 pm
There are some trace flags that if enabled may improve the performance of the integrity checks on earlier editions. I know that as of SQL Server 2016 those trace flags were moved to a database configuration setting and defaulted to ON in most cases.
Could be that you are seeing a lot of improvement because of those settings.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply