It’s been a few weeks since the PASS Summit but I’m still getting the occasional email from people who attended one of my sessions. I consider this a good thing – I believe that if someone follows up with me a few weeks after they attended a session, then something I said stuck with them and it was worth their time to follow up. Some people have had questions about something I said during a session, and others are following up on discussions we had during the week.
I had one question about minimizing the resources used by DBCC CHECKDB, and one of the suggestions I provided was to reduce MAXDOP when CHECKDB was running, to limit the number of processors used by CHECKDB. However, I want to point out that CHECKDB only runs in parallel on Enterprise Edition. You may be aware of this already, as Paul mentions it in one of his posts, and also in the SQL Server 2008 Internals book. But, as I was perusing the DBCC CHECKDB entry online, I discovered that it contains incorrect information. From BOL (emphasis mine):
“Checking Objects in Parallel
By default, DBCC CHECKDB performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured just like parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure. For more information, see Configure the max degree of parallelism Server Configuration Option. Parallel checking can be disabled by using trace flag 2528. For more information, see Trace Flags (Transact-SQL).”
The first sentence is incorrect. DBCC CHECKDB does not perform parallel checking of objects in Standard Edition. At all. CHECKDB is always single-threaded in Standard Edition and I confirmed this with the SQL Server development team.
For Enterprise Edition, CHECKDB respects the maximum degree of parallelism setting configured for the instance. If you have MAXDOP set to 0 for the instance, but want to restrict CHECKDB to only use 2 processors, you need to either change MAXDOP for the instance to 2, or you need to use Resource Governor (also an Enterprise-only feature).
I did enter a Connect item for this, and hopefully the documentation will be fixed soon. I hope this clears up any confusion that may exist.
3 thoughts on “DBCC CHECKDB Parallel Checks and SQL Server Edition”
Now you can apply a MAXDOP option to the DBCC CHECKDB command (and to DBCC CHECKTABLE and DBCC CHECKFILEGROUP commands as well). When MAXDOP is not specified with DBCC CHECKDB, the command uses the instance level “max degree of parallelism” configuration option. If the instance level configuration is 0 (default), DBCC CHECKDB could employ all the processors on the server and consume lots of resources, leaving very little room for the application workload. When a lower MAXDOP is used, less resources are used, but CHECKDB would take longer to finish.
https://blogs.msdn.microsoft.com/sqlcat/2016/08/17/sqlsweet16-episode-6-dbcc-checkdb-with-maxdop/
What about backup. Does parallel backup respect MAXDOP setting ?
There is no option to apply MAXDOP to the BACKUP command.