-
Notifications
You must be signed in to change notification settings - Fork 2
SqlMaxDop
Parameter | Attribute | DataType | Description | Allowed Values |
---|---|---|---|---|
Ensure | Write | String | When set to 'Present' then max degree of parallelism will be set to either the value in parameter MaxDop or dynamically configured when parameter DynamicAlloc is set to $true. When set to 'Absent' max degree of parallelism will be set to 0 which means no limit in number of processors used in parallel plan execution. | Present, Absent |
DynamicAlloc | Write | Boolean | If set to $true then max degree of parallelism will be dynamically configured. When this is set parameter is set to $true, the parameter MaxDop must be set to $null or not be configured. | |
MaxDop | Write | SInt32 | A numeric value to limit the number of processors used in parallel plan execution. | |
ServerName | Write | String | The host name of the SQL Server to be configured. Default value is $env:COMPUTERNAME. | |
InstanceName | Key | String | The name of the SQL instance to be configured. | |
ProcessOnlyOnActiveNode | Write | Boolean | Specifies that the resource will only determine if a change is needed if the target node is the active host of the SQL Server instance. | |
IsActiveNode | Read | Boolean | Determines if the current node is actively hosting the SQL Server instance. |
The SqlMaxDop
DSC resource set the max degree of parallelism server
configuration option. The max degree of parallelism option is used to limit
the number of processors to use in parallel plan execution. Read more about
max degree of parallelism in this article
Configure the max degree of parallelism Server Configuration Option
- If the number of configured NUMA nodes configured in SQL Server equals 1, then max degree of parallelism is calculated using number of cores divided in 2 (numberOfCores / 2), then rounded up to the next integer (3.5 > 4).
- If the number of cores configured in SQL Server are greater than or equal to 8 cores then max degree of parallelism will be set to 8.
- If the number of configured NUMA nodes configured in SQL Server is greater than 2 and the number of cores are less than 8 then max degree of parallelism will be set to the number of cores.
- Target machine must be running Windows Server 2012 or later.
- Target machine must be running SQL Server Database Engine 2012 or later.
All issues are not listed here, see here for all open issues.
This example shows how to set max degree of parallelism server configuration option with the value equal to 1.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlMaxDop 'Set_SqlMaxDop_ToOne'
{
Ensure = 'Present'
DynamicAlloc = $false
MaxDop = 1
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
This example shows how to set max degree of parallelism server configuration option with the automatic configuration.
In the event this is applied to a Failover Cluster Instance (FCI), the ProcessOnlyOnActiveNode property will tell the Test-TargetResource function to evaluate if any changes are needed if the node is actively hosting the SQL Server instance.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlMaxDop 'Set_SqlMaxDop_ToAuto'
{
Ensure = 'Present'
DynamicAlloc = $true
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
ProcessOnlyOnActiveNode = $true
}
}
}
This example shows how to set max degree of parallelism server configuration option with the default configuration.
Configuration Example
{
param
(
[Parameter(Mandatory = $true)]
[System.Management.Automation.PSCredential]
$SqlAdministratorCredential
)
Import-DscResource -ModuleName 'SqlServerDsc'
node localhost
{
SqlMaxDop 'Set_SqlMaxDop_ToDefault'
{
Ensure = 'Absent'
ServerName = 'sqltest.company.local'
InstanceName = 'DSC'
PsDscRunAsCredential = $SqlAdministratorCredential
}
}
}
- SqlAG
- SqlAGDatabase
- SqlAgentAlert
- SqlAgentFailsafe
- SqlAgentOperator
- SqlAGListener
- SqlAGReplica
- SqlAlias
- SqlAlwaysOnService
- SqlConfiguration
- SqlDatabase
- SqlDatabaseDefaultLocation
- SqlDatabaseMail
- SqlDatabaseObjectPermission
- SqlDatabaseOwner
- SqlDatabasePermission
- SqlDatabaseRecoveryModel
- SqlDatabaseRole
- SqlDatabaseUser
- SqlEndpoint
- SqlEndpointPermission
- SqlLogin
- SqlMaxDop
- SqlMemory
- SqlPermission
- SqlProtocol
- SqlProtocolTcpIp
- SqlReplication
- SqlRole
- SqlRS
- SqlRSSetup
- SqlScript
- SqlScriptQuery
- SqlSecureConnection
- SqlServerConfiguration
- SqlServerDatabaseMail
- SqlServerEndpoint
- SqlServerEndpointPermission
- SqlServerEndpointState
- SqlServerLogin
- SqlServerMaxDop
- SqlServerMemory
- SqlServerNetwork
- SqlServerPermission
- SqlServerProtocol
- SqlServerProtocolTcpIp
- SqlServerReplication
- SqlServerRole
- SqlServerSecureConnection
- SqlServiceAccount
- SqlSetup
- SqlWaitForAG
- SqlWindowsFirewall