April 12, 2016 at 11:28 am
We have recently upgraded a server from MSSQL 2008R2 Enterprise to MSSQL 2014 Enterprise. The one thing we didn't count on was a change to the database integrity checks. Using the exact same criteria and command, we have found that in 2008R2 a CHECKDB on all databases has gone from being a 1 hour task to a task that has run up to 18 hours and not completed. Our largest database is about 400GB, and running the same check on it now takes at least 4 1/2 hours to complete when ran on a low usage weekend.
Has anyone else seen an increase in times when switching to 2014 for the CheckDB command? I am seeing at a minimum 4x the time for this one database, and if this is normal, we are going to have to look at a different way to do this.
April 12, 2016 at 12:27 pm
There have been a few such issues in 2012 and 2014. Here are some of them:
https://support.microsoft.com/en-us/kb/3092702
https://support.microsoft.com/en-us/kb/3029825
http://www.sqlskills.com/blogs/paul/dbcc-checkdb-performance-and-computed-column-indexes/
What specific build are you on?
Cheers!
April 12, 2016 at 12:47 pm
Argenis Fernandez has some great stuff out there about how to make checkdb run faster, along with Paul Randal.
Check patch level. There were numerous fixes for checkdb.
My question is what about the configuration of the new server?? Is it the exact same box? What are your relevant database settings?
Have you monitored for file IO stalls and wait stats during the (attempted) runs?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 12, 2016 at 1:27 pm
We are on SQLServer 2014 SP1. We did see that there are numerous fixes to CheckDB up to SP1 CU4. I have applied that to our dev environment, but it is a much less powerful server on a less active environment. The times didn't seem to change before and after application of CU4, but I can't be certain it would not have an effect in production as they are entirely different machines.
I had seen Paul Randal's articles, but had not seen Argenis's. I will do some more reading there.
The hardware specs are the same on the server, but both the OS and MSSQL version changed. Processor, memory, disk are all the same.
I have started looking at some performance monitor counters to get more information on what is happening, and maybe areas we are having issues. I wish I had a baseline from the old server to compare.
Looks like a lot of SQL issues. I am running some tests in another environment to see if I get the same effect on another large database on another server.
April 12, 2016 at 2:27 pm
Make sure tempdb is identical, and beefy!!
I had a client that switched from Win 2012 to 2012R2 server and had a BAD experience with it and SQL Server. They actually went back to 2012. Can't recall the details.
Do you have any of the problem children that Argenis/Paul have mentioned?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply