Performance

Set-sqmMaxDop

Applies the recommended MAXDOP value (min(8, logical CPUs)) — or an explicit value — and optionally sets the matching "cost threshold for parallelism" to 50. Companion action to Test-sqmMaxDop.

Module: sqmSQLTool
Requires: dbatools
ShouldProcess: Yes (Medium)
Output: PSCustomObject

Execution Flow

START dbatools available? throw error Read current MAXDOP + CostThreshold + logical CPU count -MaxDop given? No Calculate min(8, logicalCPUs) Yes → use as-is ShouldProcess: set MAXDOP? Confirmed Set-DbaMaxDop → apply -SkipCostThreshold? Yes (skip) No Set-DbaSpConfigure CostThresholdForParallelism → -CostThreshold Return PSCustomObject (previous + new values) DONE
Microsoft recommendation: MAXDOP = min(8, logical CPU count). The default cost threshold of 5 is generally too low for modern hardware; 50 is the widely-accepted starting point. Use Test-sqmMaxDop to check current values without changing them.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-MaxDopIntOptionalExplicit MAXDOP value. Default: min(8, logical CPUs).
-CostThresholdIntOptionalCost threshold for parallelism. Default: 50. Ignored with -SkipCostThreshold.
-SkipCostThresholdSwitchOptionalOnly sets MAXDOP, leaves the cost threshold unchanged.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning a failed result.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, PreviousMaxDop, NewMaxDop, LogicalCPUs, PreviousCostThreshold, NewCostThreshold, Status, Message.

Examples

Example 1 — Apply recommended MAXDOP and cost threshold

Set-sqmMaxDop -SqlInstance "SQL01"

Example 2 — Explicit MAXDOP, skip cost threshold

Set-sqmMaxDop -SqlInstance "SQL01" -MaxDop 4 -SkipCostThreshold

Example 3 — Preview change

Set-sqmMaxDop -SqlInstance "SQL01" -WhatIf