SQL 2016 Database Scoped Configuration: MAXDOP

SQL Server 2016 Microsoft expanded the range and flexibility of database level configuration settings. There are some new features, as well as features which that can be set at more than just the server or database scope. Now, among other things, we are able to:

• set certain options both at the instance and the database level (MAXDOP)
• set instance level trace flag settings at the database level (LEGACY_CARDINALITY_ESTIMATION)
• the Trace Flag 1117 is now set at the FILEGROUP LEVEL
• the brand new database level CLEAR PROCEDURE_CACHE

A summary of the new options can be found on the Technet blog.

In this post I am going to focus on the MAXDOP feature. Having read a few articles and posts about this feature it wasn't clear to me exactly how this feature worked. Especially in regard to the relationship between the instance level setting and the database level setting.

The short answer is:

The query optimiser will use the instance level setting when the database setting is 0, otherwise the database setting overrides the instance setting. I will demonstrate this below.

I'm using a VM with 4 vCPUs. Below I set up 4 different scenarios to illustrate how the various settings affect CPU usage.

Scenario 1:
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 0 (or inactive).

/******SET SERVER MAXDOP TO MAX******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/******SET DATABASE MAXDOP TO INACTIVE (SERVER SCOPE)******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;

--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID
]]>

Scenario 2:
SQL Server Instance MAXDOP set to max and database scoped MAXDOP set to 2.

/******SET SERVER MAXDOP TO MAX******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'0' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/******SET DATABASE MAXDOP TO 2******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;

--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID

Scenario 3:
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 0 (or inactive).

/******SET SERVER MAXDOP TO 1******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/******SET DATABASE MAXDOP TO INACTIVE (SERVER SCOPE)******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;

--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID

Scenario 4:
SQL Server Instance MAXDOP set to 1 and database scoped MAXDOP set to 4.

/******SET SERVER MAXDOP TO 1******/
USE [master]
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

/******SET DATABASE MAXDOP TO 4******/
USE [Test_DB]
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 4;

--Parallel query
select *
from [dbo].[Big_Wide_Table_Archive] as a
join [dbo].[Big_Wide_Table] as b on a.ID = b.ID

I hope the above has shed some light on how this feature works. I look forward to using this feature. I am a fan of aggregating databases as much as is practical and this will certainly help as performance tuning can be that much more granular.