Calculating SQL Server Maximum Server Memory

Most of the SQL instances at my current company were set up before me, and don’t really follow any standards for assigning the maximum server memory configuration for SQL.  Since a couple of my instances were starting to starve the OS, I figured I would write a small PowerShell function to calculate what the max should be set to for a server running nothing but SQL on it.  There are lots of good resources out there about how to calculate this, but I decided to use the recommended formula found here by Jonathan Kehayias.  I also took some inspiration from the logic Chrissy LeMaire applies in her SMO recipe here, although I ended up modifying it quite a bit.

Now, my function is probably needlessly complex, but I like it that way.  Note, that it doesn’t actually set the value (although that functionality could be built on fairly easily).  It only returns the value in megabytes, which you can use/set as you please.  A link to the full function is available at the end, so don’t feel the need to try and cut and paste it together from the snippets.

The function takes a single parameter, $RAM.  This value defaults to bytes, but you can set it to whatever unit you’d like by specifying one (ie. 16GB, 16384MB, etc).  From that, it uses this basic formula from Jonathan:

Reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM

So, here goes:

$os_memoryMB    = 1024
$total_memoryMB = $ram / 1MB

Super complex, I know.  Just puts the value specified in megabytes and starts the reservation value off at 1GB.  Next is where the actual logic happens.  Here is the code for when the server has more than 4GB of RAM (hopefully always…):

if ($total_memoryMB -ge 4096) {
    $processed = 4096
    while ($processed -le $total_memoryMB){
        if ($processed -le 16384){
            # Add 1 GB to reserve for every 4 GB installed between 4 and 16 GB
            $os_memoryMB += 1024
            $processed   += 4096
            if ($processed -gt $total_memoryMB){
                # Add 1/4 GB per GB of total RAM % 4 GB
                $overage = $processed - $total_memoryMB
                $gap     = 4096 - $overage
                if ($gap -gt 0){
                    $gap_os_memoryMB = $gap * (1024 / 4096)
                    $os_memoryMB    += $gap_os_memoryMB
                }
            }
        } 
        else {
            # Add 1 GB to reserve for every 8 GB installed over 16 GB
            $os_memoryMB += 1024
            $processed   += 8192
            if ($processed -gt $total_memoryMB){
                # Add 1/8 GB per GB of total RAM % 8 GB
                $overage = $processed - $total_memoryMB
                $gap     = 8192 - $overage
                if ($gap -gt 0){
                    $gap_os_memoryMB = $gap * (1024 / 8192)
                    $os_memoryMB    += $gap_os_memoryMB
                }
            }
        }
    }
    $sql_memoryMB = $total_memoryMB - $os_memoryMB
}

What is happening here?  First, we are initializing a counter variable, $processed, to 4GB.  This is because we previously set the OS memory reservation to 1GB, which is all that needs to be done from 0-4GB.  Next, we are jumping into a while loop which will run until the amount of RAM we have processed exceeds the amount the system has available.  In that loop, we first check if we are in that 4-16GB window.  If we are, we increment $processed by 4GB and the OS memory reservation by 1GB.  We keep doing this until we reach the total system memory or 16GB.

If we reach the total, the nested if condition kicks in (if ($processed -gt $total_memoryMB){} ).  What this block does is calculate what the leftover was from the 4GB steps, basically the remainder of total system memory divided by 4GB.  It then uses that value to add an additional 1/4GB per GB of remainder to the OS memory reservation.  So, for example, if you had 10GB of total memory, the function would add 1GB initially, 1GB at the 4GB point, 1GB at the 8GB point, and have a remainder of 2GB.  It would then add 1/4GB for each of those, at a total of 512MB.  That brings the total to 6.5GB, or 6656MB.

If we reach 16GB, the else statement kicks in.  This does the exact same thing as previously, except it increments the counter by 8GB instead of 4, and adds 1/8GB for each GB remaining.  Finally, it calculates what to give SQL by subtracting the OS reservation from the total.  That brings us to the end of the function:

else {
    # Set reservation to all but 1GB for systems with < 4 GB
    $sql_memoryMB = $total_memoryMB - $os_memoryMB
    if ( $sql_memoryMB -lt 0 ){ $sql_memoryMB = 0 }
}
$sql_memoryMB

This block kicks in for sad SQL servers with less than 4GB of RAM.  For those, it either gives all but 1GB to SQL, or sets the SQL memory to 0 if there isn’t even 1GB total.  If the function ever returns 0, you should probably start trying to find your way back to 2015.  It also returns the value of $sql_memoryMB.

Here is the full function: Github ScriptCenter

You can use the -Verbose flag on the function to get information on what exactly the function is doing.