Hello Friends, There are lots of feature and improvements announced by Microsoft in SQL Server 2016, one of them is restricting the number of processors for DBCC CHECKDB. We know that MAXDOP setting is generally taken care by Instance level setting ‘MAX Degree of Parallelism’ under advanced tab of server property. I’ll show you the benefit of this feature using two different scenarios.

Scenario 1:

On my SQL Server Instance value of the MAX Degree of Parallelism setting is set to default i.e. 0. Which means SQL Server can use all the available CPU on that system (In my case I have 4 CPU on my system). If I’ll try to run DBCC CHECKDB then it can use all 4 CPU. I want to assign only 2 CPU for this command so that it will not consume all the available CPU resources.

First, Without MAXDOP setting:

DBCC CHECKDB WITH MAXDOP 1

Things to note down from above Image:

Session ID = 59, Value for max degree of parallelism = 0 (Default), Time Taken for completion = 51 Sec. Open an another query window before completion of DBCC CHECKDB command and use the session id from the above section in below TSQL execution:

DBCC CHECKDB WITH MAXDOP

Second, With MAXDOP = 3 setting (Replace this code in the same query window where session id = 59):

Run the OS task dmv again and in the output you will see that now SQL Server is using only three schedulers:

DBCC CHECKDB WITH MAXDOP

Scenario 2:

On my SQL Server Instance value of the MAX Degree of Parallelism setting is set to 1. Which means SQL Server will use a single CPU on that system (In my case I have 4 CPU on my system). If I’ll try to run DBCC CHECKDB then it will use single CPU and take longer time to complete. To complete DBCC CHECKDB faster, I can assign 4 CPU using MAXDOP.

First, without MAXDOP setting:

DBCC CHECKDB WITH MAXDOP

Things to note down from above Image:

Session ID = 59, Value for max degree of parallelism = 1, Time Taken for completion = 1 Minute 24 Sec. Check the OS tasks dmv output for session id 59:

DBCC CHECKDB WITH MAXDOP

Second, With MAXDOP = 4 setting (Replace this code in the same query window where session id = 59):

DBCC CHECKDB WITH MAXDOP

Things to note down from above Image:

Session ID = 59, Value for max degree of parallelism = 4, Time Taken for completion = 53 Sec. Check the OS tasks dmv output for session id 59:

DBCC CHECKDB WITH MAXDOP

PS: DBCC CHECKDB WITH MAXDOP also works on SQL Server 2014 SP2.

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook