If you have just begun using SQL Server 2016 or you have been using it for a while now you may not have noticed the new MAXDOP settings.

Here’s what you need to know

SQL Server 2016 introduced a really neat feature which allows you to configure MAXDOP and some other settings at the database. Simply open the database properties dialog in the UI from Object Explorer and navigate to the Options tab.

Once on the Options tab you’ll see a new section that holds configuration settings for MAXDOP, Legacy Cardinality Estimation, Parameter Sniffing, and Query Optimizer Fixes.

The default configuration for Max DOP in the Database Scope is 0. Please don’t mistake this to equal all like you see on the instance configuration in SP_CONFIGURE.

Here’s how it works

The Maximum Degree of Parallelism (MAXDOP) can be defined in one of three ways:

1. Instance Scoped via sp_configure
2. Database Scoped via database properties
3. Query Scoped via query option

Which of these trumps the other? Let’s imagine we have an instance with 32 processors:

The chart above attempts to show the following

1. A query hint always overrides the database and instance configuration
2. The database scoped configuration will override the instance configuration only when it is not the default value ( 0 ).
3. The instance scoped configuration limits MAXDOP across all databases and queries when the a query option and database scope have not been defined.

One more thing to consider is that if you use 3 part naming and execute your queries from a database without scope defined while accessing objects in a database that has a scope defined, you will not be limited.

Think of this like the following

USE TEMPDB

GO

SELECT * FROM DEMODB.DBO.Table1

This query would run under the TempDB context but accesses data in a different database. If DEMODB had defined a MAXDOP scope it would be ignored since the scope of your session is in TempDB and not DEMODB.

Here’s what this looks like in action:

Note that Query A was run from Adventureworks2012 which is configured with a database scoped Max DOP of 1. Query B ran the same query pointed at the same objects but from TempDB which has no database scoped configuration for Max DOP. The results speak for themselves. Query A was limited and B was not.

I hope that this helps you understand the new configuration settings and you’ll be able to take advantage of their awesomeness very soon.

Please also be sure to check out my other blog posts at these links:

Follow me on LinkedIn and Twitter!

http://www.sqlservercentral.com/blogs/confessions-of-a-microsoft-addict/

http://www.sqltechblog.com

http://www.sqldatapros.com