Performance

Set-sqmMaxMemory

Sets SQL Server "max server memory (MB)" to a percentage of physical RAM (default 90%) or an explicit MB value. Companion action to Test-sqmMaxMemory.

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

Execution Flow

START dbatools available? throw error Read current max server memory + total physical RAM -MaxMemoryMB given? No Calculate RAM × RecommendedPct% Yes → use as-is ShouldProcess: apply new value? Confirmed Set-DbaMaxMemory → apply Return PSCustomObject (previous + new values) DONE
Delegates the actual sp_configure call to Set-DbaMaxMemory. The change takes effect immediately without a SQL Server restart. Use Test-sqmMaxMemory to audit current settings without changing them. -RecommendedPct is validated between 70–99.

Parameters

ParameterTypeRequiredDescription
-SqlInstanceStringOptionalTarget SQL instance. Default: $env:COMPUTERNAME.
-SqlCredentialPSCredentialOptionalSQL authentication credential.
-RecommendedPctIntOptionalPercentage of physical RAM to assign (70–99). Default: 90. Ignored when -MaxMemoryMB is given.
-MaxMemoryMBIntOptionalExplicit value in MB (≥ 512). Overrides -RecommendedPct.
-EnableExceptionSwitchOptionalThrows terminating errors instead of returning a failed result.
-WhatIf / -ConfirmSwitchOptionalStandard ShouldProcess support.

Return Value

Returns a PSCustomObject with: SqlInstance, PreviousMaxMemMB, NewMaxMemMB, TotalRamMB, Status, Message.

Examples

Example 1 — Set to 90% of physical RAM

Set-sqmMaxMemory -SqlInstance "SQL01"

Example 2 — Explicit value (24 GB)

Set-sqmMaxMemory -SqlInstance "SQL01" -MaxMemoryMB 24576

Example 3 — Preview 80% setting

Set-sqmMaxMemory -SqlInstance "SQL01" -RecommendedPct 80 -WhatIf