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:
USE [master]
GO
Select @@SPID as SessionID
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism';
GO
DBCC CHECKDB('AdventureworksDW2016CTP3')
GO
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:
select * from sys.dm_os_tasks where session_id = 59
Second, With MAXDOP = 3 setting (Replace this code in the same query window where session id = 59):
USE [master]
GO
Select @@SPID as SessionID;
GO
DBCC CHECKDB('AdventureworksDW2016CTP3') WITH MAXDOP = 3;
GO
Run the OS task dmv again and in the output you will see that now SQL Server is using only three schedulers:
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:
USE [master]
GO
Select @@SPID as SessionID;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism';
GO
DBCC CHECKDB('AdventureworksDW2016CTP3');
GO
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:
select * from sys.dm_os_tasks where session_id = 59
Second, With MAXDOP = 4 setting (Replace this code in the same query window where session id = 59):
USE [master]
GO
Select @@SPID as SessionID;
GO
DBCC CHECKDB('AdventureworksDW2016CTP3') WITH MAXDOP = 4;
GO
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:
select * from sys.dm_os_tasks where session_id = 59
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
