Commands / Test-sqmMaxDop
SQL Configuration

Test-sqmMaxDop

Reads the current MAXDOP (max degree of parallelism) setting and compares it against the Microsoft recommendation of min(8, logical CPU count). Flags the special value 0 (unlimited, the SQL Server default) as unconfigured rather than merely suboptimal.

Module: sqmSQLTool
Requires: SMO, dbatools
ShouldProcess: No
Output: PSCustomObject

Execution Flow

START Get-WmiObject Win32_ComputerSystem → LogicalCPUs RecommendedDop = min(8, LogicalCPUs) Get-DbaSpConfigure -Name 'max degree of parallelism' CurrentMaxDop == 0 / == Rec / other? 0 → Unconfigured == Rec → OK else → Status = Suboptimal Return PSCustomObject (CurrentMaxDop, RecommendedDop, LogicalCPUs, Status) DONE
Suboptimal covers both directions, MAXDOP set too high (parallelism contention across concurrent queries) and too low (large queries can't use available cores). The message text tells you which. This is a read-only check; apply the recommendation with dbatools' Set-DbaSpConfigure -Name 'max degree of parallelism' -Value <n>.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringRequiredSQL Server instance name, e.g. "MSSQLSERVER" or "SERVER\INST01".

Return Value

Returns a single PSCustomObject with: SqlInstance, CurrentMaxDop, RecommendedDop, LogicalCPUs, Status (OK, Suboptimal, Unconfigured, or Error), Message.

Examples

Example 1, Check the default instance

Test-sqmMaxDop -SqlInstance 'MSSQLSERVER'

Example 2, Check a named instance

Test-sqmMaxDop -SqlInstance 'SQL01\INST01'
⬆ Index
All Commands