Setting the Max Memory in SQL Server

See the source image

Here is a quick post about setting the max memory in SQL Server so that you got no worries if you forgot to set this at installation.

I get it we all been there before. This was a step I over looked when starting out with SharePoint many years ago. However I did recently have this come up in a discussion and I remembered and thought I should do this in PowerShell to exercise my scripting technique. It seems to be best used as an expression.


Test this in a dev environment first and not production to ensure compatibility with versions and the .net framework:

The Nitty Gritty:

Function Set-SQLServerMemory

    write-verbose "Begining"
     #$report+=New-Object -TypeName psobject -Property @{Servername="testServer"; TotalMemory=8076;} | select Servername,TotalMemory


    write-verbose "Begining the Process"

    foreach($ServerName in $ServerNames){
    Write-Verbose "In The Loop"
            $Rawmemorytotal = Get-wmiobject -ComputerName $ServerName -class Win32_PhysicalMemory | Measure-Object -Property Capacity -Sum
                                                                TotalMemory=($Rawmemorytotal.Sum / 1MB)
            $min_os_memory = 2048 
            if( $TotalMemory -le $min_os_memory) {
                Write-Host "Memory too Low, Server could be Serverly impacted." -ForegroundColor Red 
            if ($TotalMemory -ge 8192) {
                $sql_mem = $TotalMemory - $min_os_memory
            } else {
                $sql_mem = $TotalMemory * 0.79 ;

    if ($MinMemory -eq 0) {
            $MinMemory = $MaxMemory
        [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
        $SQLServer = New-Object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
        if ($SQLServer.status) {
              $SQLServer.Configuration.MaxServerMemory.ConfigValue = $MaxMemory
            $SQLServer.Configuration.MinServerMemory.ConfigValue = $MinMemory   
            }#end if

        }#end Foreach

        return $report | select Servername,TotalMemory
        #return [int]$sql_mem ;  



Published by Xajuan Smith


3 thoughts on “Setting the Max Memory in SQL Server

  1. Why did you choose to use Get-WMIObject instead of Get-CIMInstance?

    It’s always interesting to see this being done versus ‘dbatools’ module “Set-DbaMaxMemory” (Set-DbaMaxMemory -SqlInstance $env:computername)

    Thanks for sharing your script!


    1. That’s a great question. Dbatools is a great solution. Some environments prohibit access to download modules and access to certain things because of JEA ideas.

      I used this approach because it’s not dependent on a module. Empowering admins to tap .net can allow for self built tools that encourage deep learning. I have seen the awesome stuff that dbatools provide. Sometimes these solutions is over simplifying the automation experience and not encouraging hands on and scripting adoption.

      Sorry that was a really long answer lol.

      Liked by 1 person

    2. I also used get-wmiobject out of habit. I still struggle switching to using get-ciminstance. So because you helped me remember In some environments ciminstance doesn’t communicate well with older environments as well due to is deployed security. It would take more effort to make that available in slow moving organizations.

      Liked by 1 person

Leave a Reply


You are commenting using your account. Log Out /  Change )

Google photo


Twitter picture


Facebook photo


Connecting to %s

