May 17, 2018 at 2:05 pm
I am completely lost here and not sure what else to look for. Came across a very interesting problem that i wouldn't have anticipated after getting on 2016 from 2008R2. I confirmed there is no IO bottleneck, same database is restored on another instance same server on same drive. Basically checkdb on another sql instance on same server runs 4 X faster, i see the faster one is using less CPU, however when same check db is run on same database on another instance same server it is 4 X slower,also notice cpu usage is 4X. I confirmed DBCC checkdb is not parallelizing, infact there is significantly more RAM on the instance that it is running slower. Yes there is about 40% CPU usage, mostly from the instance that i am complaining about however i wouldn't expect checkdb to be 4 X slow. I am not sure what is causing this. Both the instances are also at same sp level. Would like to know if any one else has experienced this?
May 17, 2018 at 3:55 pm
curious_sqldba - Thursday, May 17, 2018 2:05 PMI am completely lost here and not sure what else to look for. Came across a very interesting problem that i wouldn't have anticipated after getting on 2016 from 2008R2. I confirmed there is no IO bottleneck, same database is restored on another instance same server on same drive. Basically checkdb on another sql instance on same server runs 4 X faster, i see the faster one is using less CPU, however when same check db is run on same database on another instance same server it is 4 X slower,also notice cpu usage is 4X. I confirmed DBCC checkdb is not parallelizing, infact there is significantly more RAM on the instance that it is running slower. Yes there is about 40% CPU usage, mostly from the instance that i am complaining about however i wouldn't expect checkdb to be 4 X slow. I am not sure what is causing this. Both the instances are also at same sp level. Would like to know if any one else has experienced this?
Do both instances have the same configurations, settings?
Is this a virtual server? Anything set for lock pages in memory for either instances service account?
Did you check available space differences for tempdb, log?
Sue
May 19, 2018 at 11:14 am
i)
Sue_H - Thursday, May 17, 2018 3:55 PMcurious_sqldba - Thursday, May 17, 2018 2:05 PMI am completely lost here and not sure what else to look for. Came across a very interesting problem that i wouldn't have anticipated after getting on 2016 from 2008R2. I confirmed there is no IO bottleneck, same database is restored on another instance same server on same drive. Basically checkdb on another sql instance on same server runs 4 X faster, i see the faster one is using less CPU, however when same check db is run on same database on another instance same server it is 4 X slower,also notice cpu usage is 4X. I confirmed DBCC checkdb is not parallelizing, infact there is significantly more RAM on the instance that it is running slower. Yes there is about 40% CPU usage, mostly from the instance that i am complaining about however i wouldn't expect checkdb to be 4 X slow. I am not sure what is causing this. Both the instances are also at same sp level. Would like to know if any one else has experienced this?Do both instances have the same configurations, settings?
Is this a virtual server? Anything set for lock pages in memory for either instances service account?
Did you check available space differences for tempdb, log?Sue
i) Both the instances are on the same server.
ii) This is a physical server. Infact the server where dbcc is running has more RAM. Since both instances are on the same server, yeah LPM in enabled.
May 19, 2018 at 1:29 pm
curious_sqldba - Saturday, May 19, 2018 11:14 AMi)Sue_H - Thursday, May 17, 2018 3:55 PMDo both instances have the same configurations, settings?
Is this a virtual server? Anything set for lock pages in memory for either instances service account?
Did you check available space differences for tempdb, log?Sue
i) Both the instances are on the same server.
ii) This is a physical server. Infact the server where dbcc is running has more RAM. Since both instances are on the same server, yeah LPM in enabled.
The instances being on the same server doesn't mean they have the same configurations. You can different versions of SQL Server on the same server. Configurations are done by instance, not server.
LPIM is not done by server either. One instance could have it set and another on the same server may not have it set.
And tempdb and log sizes, settings can make a difference and can also be different for each instance.
You may want to consider that things could be different between the two instances even if they are on the same server.
Sue
May 19, 2018 at 3:38 pm
Sue_H - Saturday, May 19, 2018 1:29 PMcurious_sqldba - Saturday, May 19, 2018 11:14 AMi)Sue_H - Thursday, May 17, 2018 3:55 PMDo both instances have the same configurations, settings?
Is this a virtual server? Anything set for lock pages in memory for either instances service account?
Did you check available space differences for tempdb, log?Sue
i) Both the instances are on the same server.
ii) This is a physical server. Infact the server where dbcc is running has more RAM. Since both instances are on the same server, yeah LPM in enabled.
The instances being on the same server doesn't mean they have the same configurations. You can different versions of SQL Server on the same server. Configurations are done by instance, not server.
LPIM is not done by server either. One instance could have it set and another on the same server may not have it set.
And tempdb and log sizes, settings can make a difference and can also be different for each instance.You may want to consider that things could be different between the two instances even if they are on the same server.
Sue
I am.very well aware of the settings, when I mewnt everything is same , the settings are all same at instance.
May 24, 2018 at 12:17 am
I'm having a similar issue in my environment too. Just wondering are you using the Ola scripts to run the checkdb? are you using data_purity or physical_only option?
Might be worth doing a checkdb a table at a time to find which table is causing the performance issue.
Also check if your disk types are set up correctly including the blocksize.
Danny
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply