December 2, 2018 at 4:39 am
I understand that in order to have DBCC CHECKDB use parallel threads you need to be on Enterprise Edition,
and likewise with Index Rebuilds.
We went to Enterprise Edition, and immediately the runtime of DBCC CHECKDB reduced by over 50%, and
we also saw a reduction in the time taken to run Index rebuilds.
Using SolarWinds, I can see that Index Rebuild appeared to use parallel threads but I am confused as to what it
shows for DBCC CHECKDB.
I see CXPACKET waits, but it reports a solid 60 seconds SQL Time in every minute that DBCC CHECKDB is running,
which is usually an indicator (to me anyway) that the SPID is only using 1 processor (No parallelism). and driving
it at 100%.
We are running 2014 Enterprise Edition 64 Bit (SP3) - which I just verified (and see it now supports MAXDOP for
Statistics updates - which is interesting as an aside).
MAXDOP is set to 6 at the instance level (6 processors in each Numa node) and looking in Resource Governor all I see
are 2 Profiles (Default and Internal) both with MAXDOP set to 0. There is no MAXDOP specified on the command
execution.
I will log in and run sp_who3 tonight while DBCC CHECKDB runs to verify what the SPID is doing as I seem to have
ticked all of the boxes yet what SolarWinds tells me is confusing me - so any wisdom would be appreciated.
Regards
Steve O.
December 3, 2018 at 5:30 am
Well I did log in and I see multiple threads in sp_who3 (that align with the CXPACKET waits I see).
I also see that CPU% recorded in SolarWinds (when the only activity is CHECKDB) is too high for 1 processer,
With 12 processors I expect 8.3% busy for 1 processor and I see and average of 30%+.
So this may be a SolarWinds thing - unless anybody has any other ideas.
I would be interested in other ways to look at this kind of thing (DMVs?) if anybody has any ideas.
Thanks.
Steve O.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply