Minion CheckDB allows you to run multiple DBCC CheckDB processes, or multiple DBCC CheckTable processes, at the same time.
To configure database multithreading, set the NumConcurrentOps value greater than one in Minion.CheckDBSettingsServer. This applies to both DBCC CheckDB and DBCC CheckTable operations.
To configure table multithreading, set the DBInternalThreads value greater than one in Minion.CheckDBSettingsServer (or in Minion.CheckDBSettingsDB). Note: If you specify DBInternalThreads in Minion.CheckDBSettingsServer, that value takes precedence over the DBInternalThreads setting in Minion.CheckDBSettingsDB.
Warning: You can max out server resources very quickly if you use too many concurrent operations. If for example you’re running 5 databases simultaneously, and each of those operations runs 10 tables simultaneously, that can add up very quickly!
IMPORTANT: Custom dynamic snapshots for CheckTable are only available for single-threaded operations. This means that you must set DBInternalThreads in Minion.CheckDBSettingsDB, and DBInternalThreads in Minion.CheckDBSettingsServer, to 1 for custom dynamic snapshots. For more information, see the Custom Dynamic Snapshots section in “About: Custom Snapshots”; and, see “About: Feature Compatibility”.
Multithreading information is logged in Minion.CheckDBLogDetails. In a multithreaded run, the ProcessingThread column records number of the thread assigned to this operation. You can use this to query with GROUP BY to see the distribution of threads (e.g., did one thread handle most of the work, or was there a reasonably good distribution of work?)
On DisableDOP and “parallel checking”
In SQL Server Enterprise, by default a DBCC CheckDB operation runs with multiple parallel threads under the covers. If you set DisableDOP=1, you force it to use a single thread, instead of multiple threads. In Minion CheckDB, we have a completely separate (but compatible) concept called database multithreading; this is where we spawn two or more DBCC CheckDB operations to run simultaneously.
| DisableDOP = 0 | DisableDOP = 1 | |
|---|---|---|
| Database Multithreading on | Multiple DBs process simultaneously; each may have multiple parallel threads. | Multiple DBs process simultaneously; each may have only one thread. |
| Database Multithreading off | Each DB is processed serially; each may have multiple parallel threads. | Each DB is processed serially; each may have only one thread |
Checking Objects in Parallel – from https://msdn.microsoft.com/en-us/library/ms176064.aspx: “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).”