SQLServer 2016 New feature, MAXDOP option in DBCC CHECKDB.

prior sql server 2016, Maxdop can be configured using sp_configure to setup max degree of parallelism at server level for Database maintenance(DBCC CHECKDB)

Prior SQL2016 Code:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO

With 2016 SQL server, MAXDOP can be as an option with DBCC CHECKDB at Database,Table and Filegroup level.

DBCC CHECKDB(N’test’) WITH MAXDOP = 8

USE test
GO

DBCC CHECKTABLE(‘dbo.t1’) WITH MAXDOP = 8
DBCC CHECKFILEGROUP(2) WITH MAXDOP =8

 

Test results:

In this test, I have created secondary file group.

table t2 is created on secondary filegroup and ran the following command

DBCC CHECKFILEGROUP(2) WITH MAXDOP =8

table t1 cannot be checked because it resides on primary file group, table t2 is  checked since  fg2 for DBCC checkdb.

 

 

There are 0 rows in 0 pages for object "sys.plan_persist_context_settings".
Cannot process rowset ID 72057594041270272 of object "t1" (ID 565577053), index "t1" (ID 0), because it resides on filegroup "PRIMARY" (ID 1), which was not checked.
DBCC results for 't1'.
There are 0 rows in 0 pages for object "t1".
DBCC results for 't2'.
There are 0 rows in 0 pages for object "t2".

 

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