SQL Server DBCC CHECKDB – Going Parallel

DBCC CHECKDB has the ability to perform parallel checking of objects. However, it absolutely depends on the edition of SQL Server, it only happens when using enterprise edition.

Let’s see this in action. I propose the following tests for this blog post:

  • Test on a SQL Server Enterprise Edition.
  • Test on a non-enterprise edition of SQL Server.

I don’t have 2017 Enterprise at hand but I do have 2014 Enterprise and Express handy so it makes sense to use these versions for my “experiment”.

Let’s look at the express edition first.

I run CHECKDB in 1 window and track it via a session_id using the following query taken from (Note: this is NOT my query – credit to the author in the link): https://www.brentozar.com/archive/2014/11/many-cpus-parallel-query-using-sql-server


DBCC CHECKDB ('AdventureWorks2020')

checkdb1thread

Each scheduler is mapped to a vCPU on my machine. Taking a step back, I have 4 vCPUs so you will see the 4 schedulers utilised if the CHECKDB goes parallel (assuming I haven’t changed anything like processor affinity masking etc) – which obviously needs Enterprise edition.

So moving over to the Enterprise:

ENTEdition

DBCC CHECKDB ('AdventureWorks2020')

Using the same query above we get parallelism – look at the colour boxes.

4cpus

If I change MAXDOP to 2 you will see a slight difference:

DBCC CHECKDB ('AdventureWorks2020') WITH MAXDOP = 2

2cpus

If you don’t like parallel checks, then you can disable it by using Trace Flag 2528

DBCC TRACEON (2528,-1)
DBCC CHECKDB ('AdventureWorks2020')

OFFPARA

What part of CHECKDB goes parallel?

Based on my findings it looks like DBCC CHECKTABLE does.

USE [NEWdb]
go
DBCC CHECKALLOC

ALLOC

DBCC CHECKCATALOG

CATALOG

DBCC CHECKTABLE ('dbo.continue')

CHECKTABLES

Remember you can overrule parallelism for DBCC CHECKTABLE with the above trace flag too, if you really want to.

 

2 thoughts on “SQL Server DBCC CHECKDB – Going Parallel

  1. Pingback: Parallel CHECKDB – Curated SQL

  2. Pingback: Dew Drop - December 19, 2017 (#2627) - Morning Dew

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s