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…

Advertisements

3 thoughts on “Setting “SQL Max Memory” with Powershell

  1. Pingback: Powershell – CalculateSQLMaximumMemory v2 | Sveroa's Developer Blog

  2. Regarding setting the SQL server max memory :
    I tried doing this but it fails to change the configuvalue

    $sStrSQLServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) localhost
    $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    $sStrSQLServer.Alter()

    So I tried below

    $sStrSQLServer = New-Object Microsoft.SqlServer.Management.Smo.Server localhost
    $sStrSQLServer.ConnectionContext.LoginSecure=$false;
    $sStrSQLServer.ConnectionContext.set_Login($sStrSAUserAccountName)
    $sStrSQLServer.ConnectionContext.set_Password($sStrSAUserPassword)

    try
    {
    $sStrSQLServer.ConnectionContext.Connect()
    $sStrSQLServer.Configuration.MaxServerMemory.ConfigValue = 4000
    $sStrSQLServer.Configuration.Alter()
    }

    But above fails with error as Exception setting “ConfigValue”:Failed to retrieve data for the request

    What am I missing above

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