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


Set-DbaMaxDop

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

Screenshots

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

 

Examples

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.

Get-Help

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