functions/Set-DbaMaxDop.ps1
|
function Set-DbaMaxDop { <# .SYNOPSIS Sets SQL Server max dop then displays information relating to SQL Server Max DOP configuration settings. Works on SQL Server 2005-2016. .DESCRIPTION Uses the Test-DbaMaxDop command to get the recommended value if -MaxDop parameter is not specified. These are just general recommendations for SQL Server and are a good starting point for setting the "max degree of parallelism" option. You can set MaxDop database scoped configurations if the server is version 2016. THIS CODE IS PROVIDED "AS IS", WITH NO WARRANTIES. .PARAMETER SqlInstance Allows you to specify a comma separated list of servers to query. .PARAMETER SqlCredential Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted. To use: $cred = Get-Credential, this pass this $cred to the param. Windows Authentication will be used if DestinationSqlCredential is not specified. To connect as a different Windows user, run PowerShell as that user. .PARAMETER Database The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will be processed. .PARAMETER ExcludeDatabase The database(s) to exclude - this list is auto-populated from the server .PARAMETER MaxDop Allows you to specify the MaxDop value that you want to use. .PARAMETER AllDatabases This is a parameter that was included so you can set MaxDop value to all databases. Only valid when using on SQL Server 2016 instances. .PARAMETER WhatIf Shows what would happen if the command were to run. No actions are actually performed. .PARAMETER Confirm Prompts you for confirmation before executing any changing operations within the command. .PARAMETER Collection Results of Test-SQLMaxDop to be passed into the command .NOTES Author: Claudio Silva (@claudioessilva) Website: https://dbatools.io Copyright: (C) Chrissy LeMaire, clemaire@gmail.com License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0 .LINK https://dbatools.io/Set-DbaMaxDop .EXAMPLE Set-DbaMaxDop -SqlInstance sql2008, sql2012 Set recommended Max DOP setting for servers sql2008 and sql2012. .EXAMPLE Set-DbaMaxDop -SqlInstance sql2014 -MaxDop 4 Set Max DOP setting to 4 for server sql2014. .EXAMPLE Test-DbaMaxDop -SqlInstance sql2008 | Set-DbaMaxDop Get Max DOP recommended setting from Test-DbaMaxDop and applies to sql2008 instance .EXAMPLE Set-DbaMaxDop -SqlInstance sql2016 -Database db1 Set recommended Max DOP setting database db1 on server sql2016. .EXAMPLE Set-DbaMaxDop -SqlInstance sql2016 -AllDatabases Set recommended Max DOP setting for all databases on server sql2016. #> [CmdletBinding(SupportsShouldProcess = $true)] param ( [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)] [Alias("ServerInstance", "SqlServer")] [DbaInstanceParameter[]]$SqlInstance, [Alias("Credential")] [PSCredential] $SqlCredential, [Alias("Databases")] [object[]]$Database, [object[]]$ExcludeDatabase, [int]$MaxDop = -1, [Parameter(ValueFromPipeline = $True)] [object]$Collection, [Alias("All")] [switch]$AllDatabases ) begin { if ($Database -and $AllDatabases -and $ExcludeDatabase) { throw "-Database, -AllDatabases and -ExcludeDatabase are mutually exclusive. Please choose only one. Quitting" } $processed = New-Object System.Collections.ArrayList $results = @() } process { $dbscopedconfiguration = $false if ($MaxDop -eq -1) { $UseRecommended = $true } if ($collection -eq $null) { $collection = Test-DbaMaxDop -SqlInstance $sqlinstance -SqlCredential $SqlCredential -Verbose:$false } elseif ($collection.Instance -eq $null) { $collection = Test-DbaMaxDop -SqlInstance $sqlinstance -SqlCredential $SqlCredential -Verbose:$false } $collection | Add-Member -Force -NotePropertyName OldInstanceMaxDopValue -NotePropertyValue 0 $collection | Add-Member -Force -NotePropertyName OldDatabaseMaxDopValue -NotePropertyValue 0 $servers = $collection | Select-Object Instance -Unique foreach ($server in $servers) { if ($server.Instance -ne $null) { $servername = $server.Instance } else { $servername = $server } Write-Verbose "Attempting to connect to $servername" try { $server = Connect-SqlInstance -SqlInstance $servername -SqlCredential $SqlCredential } catch { Write-Warning "Can't connect to $server or access denied. Skipping." continue } if (!(Test-SqlSa -SqlInstance $server)) { Write-Error "Not a sysadmin on $server. Skipping." $server.ConnectionContext.Disconnect() continue } if ($server.versionMajor -ge 13) { Write-Verbose "Server '$servername' supports Max DOP configuration per database." if (!$Database -and !$ExcludeDatabase) { #Set at instance level $collection = $collection | Where-Object { $_.DatabaseMaxDop -eq "N/A" } } else { $dbscopedconfiguration = $true if (!$AllDatabases -and $Database) { $collection = $collection | Where-Object { $_.Database -in $Database } } elseif (!$AllDatabases -and $ExcludeDatabase) { $collection = $collection | Where-Object { $_.Database -notin $ExcludeDatabase } } else { if ($AllDatabases) { $collection = $collection | Where-Object { $_.DatabaseMaxDop -ne "N/A" } } else { $collection = $collection | Where-Object { $_.DatabaseMaxDop -eq "N/A" } $dbscopedconfiguration = $false } } } } else { if ($database -or $AllDatabases) { Write-Warning "Server '$servername' (v$($server.versionMajor)) does not support Max DOP configuration at the database level. Remember that this option is only available from SQL Server 2016 (v13). Run the command again without using database related parameters. Skipping." Continue } } foreach ($row in $collection | Where-Object { $_.Instance -eq $servername }) { if ($UseRecommended -and ($row.RecommendedMaxDop -eq $row.CurrentInstanceMaxDop) -and !($dbscopedconfiguration)) { Write-Output "$servername is configured properly :) No change required." Continue } if ($UseRecommended -and ($row.RecommendedMaxDop -eq $row.DatabaseMaxDop) -and $dbscopedconfiguration) { Write-Output "Database $($row.Database) on $servername is configured properly :) No change required." Continue } $row.OldInstanceMaxDopValue = $row.CurrentInstanceMaxDop try { if ($UseRecommended) { if ($dbscopedconfiguration) { $row.OldDatabaseMaxDopValue = $row.DatabaseMaxDop if ($resetDatabases) { Write-Verbose "Changing $($row.Database) database max DOP to $($row.DatabaseMaxDop)." $server.Databases["$($row.Database)"].MaxDop = $row.DatabaseMaxDop } else { Write-Verbose "Changing $($row.Database) database max DOP from $($row.DatabaseMaxDop) to $($row.RecommendedMaxDop)" $server.Databases["$($row.Database)"].MaxDop = $row.RecommendedMaxDop $row.DatabaseMaxDop = $row.RecommendedMaxDop } } else { Write-Verbose "Changing $server SQL Server max DOP from $($row.CurrentInstanceMaxDop) to $($row.RecommendedMaxDop)" $server.Configuration.MaxDegreeOfParallelism.ConfigValue = $row.RecommendedMaxDop $row.CurrentInstanceMaxDop = $row.RecommendedMaxDop } } else { if ($dbscopedconfiguration) { $row.OldDatabaseMaxDopValue = $row.DatabaseMaxDop Write-Verbose "Changing $($row.Database) database max DOP from $($row.DatabaseMaxDop) to $MaxDop" $server.Databases["$($row.Database)"].MaxDop = $MaxDop $row.DatabaseMaxDop = $MaxDop } else { Write-Verbose "Changing $servername SQL Server max DOP from $($row.CurrentInstanceMaxDop) to $MaxDop" $server.Configuration.MaxDegreeOfParallelism.ConfigValue = $MaxDop $row.CurrentInstanceMaxDop = $MaxDop } } if ($dbscopedconfiguration) { if ($Pscmdlet.ShouldProcess($row.Database, "Setting max dop on database")) { $server.Databases["$($row.Database)"].Alter() } } else { if ($Pscmdlet.ShouldProcess($servername, "Setting max dop on instance")) { $server.Configuration.Alter() } } $results += [pscustomobject]@{ ComputerName = $server.NetName InstanceName = $server.ServiceName SqlInstance = $server.DomainInstanceName InstanceVersion = $row.InstanceVersion Database = $row.Database DatabaseMaxDop = $row.DatabaseMaxDop CurrentInstanceMaxDop = $row.CurrentInstanceMaxDop RecommendedMaxDop = $row.RecommendedMaxDop OldDatabaseMaxDopValue = $row.OldDatabaseMaxDopValue OldInstanceMaxDopValue = $row.OldInstanceMaxDopValue } } catch { Write-Error "Could not modify Max Degree of Paralellism for $server." } } if ($dbscopedconfiguration) { $results | Select-Object Instance, Database, OldDatabaseMaxDopValue, @{ name = "CurrentDatabaseMaxDopValue"; expression = { $_.DatabaseMaxDop } } } else { $results | Select-Object Instance, OldInstanceMaxDopValue, CurrentInstanceMaxDop } } } } |