Powershell – CalculateSQLMaximumMemory v2


In my last post on setting the “Maximum Server Memory” for SQL Server, I described a simple method of calculutaing this number by using the 80% rule. This post will describe a maybe better way of calculating the “Max memory”.

During the testing an development of this new version of CalculateSQLMaximumMemory function, I discovered error in my original “max memory” post. These errors are corrected and a new table added.

This new method is better for server with less than 32GB memory.

function CalculateSQLMaximumMemory([int]$memtotal = 0)
{
    if ($memtotal -eq 0)
    {
        $tmp = Get-WMIObject -class Win32_PhysicalMemory `
                    | Measure-Object -Property capacity -Sum 
        $memtotal = $tmp.Sum / 1MB;
    }

    if ($memtotal -lt 3072)
    {
        Write-Host "Not recommended with less than 3GB memory";
        Return $null;
    }

    $os_mem = 2048 ;
    $tot = 4096 ;
    $inc_mb = 4096 ;

    while ($tot -lt $memtotal)
    {
        $tot += $inc_mb ;
        $os_mem += 1024 ;

        if ($tot -eq 16384)
        {
            $inc_mb = 8192;
        }
    }

    $sql_mem = $memtotal - $os_mem ;
    return $sql_mem ;
}

Examples:

$mem = CalculateSQLMaximumMemory ;
Write-Host "My local max memory: " $mem ;

$mem = CalculateSQLMaximumMemory 4096 ;
Write-Host "  4GB = SQL: " $mem ;

$mem = CalculateSQLMaximumMemory 8192 ;
Write-Host "  8GB = SQL: " $mem ;

$mem = CalculateSQLMaximumMemory 16384 ;
Write-Host " 16GB = SQL: " $mem ;

$mem = CalculateSQLMaximumMemory 32768;
Write-Host " 32GB = SQL: " $mem ;

$mem = CalculateSQLMaximumMemory 65536;
Write-Host " 64GB = SQL: " $mem ;

In the post install script, you will something like:

$maxmemory = CalculateSQLMaximumMemory ;
SetSQLInstanceMemory "." $maxmemory ;

Happy coding….

Advertisements

2 thoughts on “Powershell – CalculateSQLMaximumMemory v2

  1. Pingback: Setting “SQL Max Memory” with Powershell | Sveroa's Developer Blog

  2. Pingback: Unattended install for SQL Server 2008 R2 | Sveroa's Developer Blog

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s