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
            $report+=New-Object -TypeName psobject -Property @{ 
                                                                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

Computer Information Specialist, with a desire to make the masses feel what I was born to channel...The emotion that strives us to do more, speak more boldly and with most sincere ideals that will make life easier and will not hinder the dreams we all have at any given time.

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

Fill in your details below or click an icon to log in: Logo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: