Commands / Test-sqmCostThreshold
Diagnostics & Health

Test-sqmCostThreshold

Reads the current cost threshold for parallelism via SMO and flags it if it's below the recommended minimum (default 50). SQL Server's own default of 5 is far too low for modern hardware and triggers unnecessary parallel execution for cheap queries.

Module: sqmSQLTool
Requires: SMO, SQL connection
ShouldProcess: No
Output: PSCustomObject

Execution Flow

START MinRecommendedValue: explicit param, else Get-sqmConfig 'CheckCostThresholdMin', else 50 SMO connect → Server.Configuration.CostThresholdForParallelism.RunValue CurrentValue >= MinRecommendedValue? Status = OK Status = Warning Return PSCustomObject (CurrentValue, RecommendedMinValue, Status) DONE On connection/SMO error: Status = Error, -EnableException throws, else Write-Error
This is a read-only diagnostic — it never changes the setting. To apply the recommendation, use Set-sqmServerSetting or dbatools' Set-DbaSpConfigure -Name 'cost threshold for parallelism' -Value 50. Pairs naturally with Test-sqmMaxDop as the two classic "first things to check" on a new SQL Server instance.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalSQL Server instance. Default: local computer name.
-SqlCredentialPSCredentialOptionalSQL authentication credential for the connection.
-MinRecommendedValueInt (1–32767)OptionalMinimum acceptable value. Default: 50, or the module config key CheckCostThresholdMin if set and this parameter wasn't given explicitly.
-EnableExceptionSwitchOptionalThrow a terminating exception instead of Write-Error.

Return Value

Returns a single PSCustomObject with: SqlInstance, CurrentValue, RecommendedMinValue, Status (OK, Warning, or Error), Message.

Examples

Example 1 — Check against the default minimum (50)

Test-sqmCostThreshold -SqlInstance "SQL01"

Example 2 — Use a custom minimum

Test-sqmCostThreshold -SqlInstance "SQL01" -MinRecommendedValue 25

Example 3 — Sweep a server list, show only warnings

"SQL01","SQL02" | ForEach-Object { Test-sqmCostThreshold -SqlInstance $_ } |
    Where-Object Status -eq 'Warning'
⬆ Index
All Commands