Understanding the new MAXDOP settings in SQL 2016

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.

image

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:

image

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:

image

image

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

One thought on “Understanding the new MAXDOP settings in SQL 2016

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