dbatools is a free PowerShell module with over 300 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


This command will help you set MaxDop configuration according to Microsoft recommendations.

If you want to know how we calculate the recommended value you can do it by visiting the Test-DbaMaxDop command page.

Keep in mind that we only will use recommended value if you don’t provide -MaxDop parameter.

Can I set MaxDop value per database?

If you are using SQL Server 2016 version you can! Just provide the -Databases parameter with one or more databases. If you want to set to all databases just use -AllDatabases parameter without the -Databases one.

See screenshots and examples.

NOTE: Instances running version different from SQL Server 2016 will be skipped when using -AllDatabases


Set MaxDop on sql2008 and sql2012 server using recommended value
Set MaxDop on sql2008 and sql2012 server using recommended value
Set maxdop value to 4 on sql2012 server
Set maxodp using Test-DbaMaxDop recommended value to sql2008
Setting MaxDop to sql2005 and sql2016 instances where sql2016 is already configured properly
Set MaxDop to sql2016 instance
Set MaxDop to specific database ‘db1’ on 2016 instance
Set MaxDop to all databases on sql2016 instance



Set Max DOP setting to 4 for server sql2014.

Set-DbaMaxDop -SqlServer sql2014 -MaxDop 4

Get Max DOP recommended setting from Test-DbaMaxDop and applies to sql2008 instance

Test-DbaMaxDop -SqlServer sql2008 | Set-DbaMaxDop

Set recommended Max DOP setting database db1 on server sql2016.

Set-DbaMaxDop -SqlServer sql2016 -Databases db1

Set recommended Max DOP setting for all databases on server sql2016.

Set-DbaMaxDop -SqlServer sql2016 -AllDatabases

This command was created by Cláudio Silva. You can find Cláudio on Twitter and LinkedIn.


From PowerShell, execute Get-Help Set-DbaMaxDop -Detailed for more information on this function.

Source Code

Want to see the source code? View Set-DbaMaxDop.ps1 on GitHub

Related commands