SQL Server 2016 – MAXDOP per database

Max Degree of Parallellism (MAXDOP) is a setting you can use to limit the number of parallell threads SQL server can use for parallell queries. Parallellism is a good thing in most cases, but for some OLTP systems with many short queries you can experience performance problems. The common solution used to be to use MAXDOP to limit number of threads on the SQL Server instance, but that setting affects every database in the instance! That can cause other performance problem for all the other databases.

In SQL Server 2016, you can change the MAXDOP for the specific database and not the whole instance. This is a setting is now a database setting and that setting is also applied to a secondary database in Always On Availability Group.  By setting this setting on the primary database could have affected the performance of a readable secondary where you may want to have a higher MAXDOP for analysis queries. But Microsoft implemented a MAXDOP setting for the primary database and one setting for the secondary! 🙂

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = 4;
GO

In this sample I set MAXDOP to 2 for the primary database and MAXDOP to 4 for the replicas of the AlwaysOn Availability Groups.