Setting “SQL Max Memory” with Powershell

EDIT: see version2 of the GetSQLMaxMemory function. added “$sql_mem -= ($sql_mem % 1024)” before returning from GetSQLMaxMemory. This is done to truncate to nearest MB.

When installing SQL Server, you perform a set of pre- and post installation tasks. One of these tasks is to set the “Maximum Server Memory” for each instance on your new server. There are a few different “Rules of Thumbs” described in this post. I will choose the simplest version for my first version of these powershell functions where I choose to set max Memory to 80% of total physical memory on the server.

The powershell function GetSQLMaxMemory will find the total physical memory. It is not recommended to install SQL Server on servers with less than 2GB of memory. Therefore, this function will return NULL of this is true.

function GetSQLMaxMemory()
{
    $mem = Get-WMIObject -class Win32_PhysicalMemory `
                     | Measure-Object -Property capacity -Sum 
    $memtotal = ($mem.Sum / 1MB);
    $min_os_mem = 2048 ;
    if ($memtotal -le $min_os_mem)
        Return $null;
    $sql_mem = $memtotal * 0.8 ;
    $sql_mem -= ($sql_mem % 1024) ;  
    return $sql_mem ;
}

After you have used the GetSQLMaxMemory function or calculated this manually, you can use the following Powershell function SetSQLInstanceMemory to set the minimum and maximum server memory for gievn instance.

function SetSQLInstanceMemory ( 
    [string]$SQLInstanceName = ".", 
    [int]$maxMem = $null, 
    [int]$minMem = $null) 
{
    [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") `
                             | Out-Null
    $srv = new-object Microsoft.SQLServer.Management.Smo.Server($SQLInstanceName)
    $srv.ConnectionContext.LoginSecure = $true

    $srv.Configuration.MaxServerMemory.ConfigValue = $maxMem
    $srv.Configuration.MinServerMemory.ConfigValue = $minMem

    $srv.Configuration.Alter()
}

Happy coding…