Tuesday, 2 January 2018

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:
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).


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


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


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

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.

1 comment:

Sulekha Nivas said...

Thank you for sharing the useful information.

PYTHON Training in Chennai