Automatic Memory Management for SQL Server Failover Cluster Instances

One of the challenges while using Failover Cluster Instances (FCI) is to manage the memory of the SQL server in case of failover. In general, most SQL Server configurations do not require much attention to memory management beyond the initial configuration. However, Failover Cluster Instances (FCI) architecture is very different from others. Therefore, special attention should be paid to automatic memory management. Here You can read more about FCI.

Initial memory configuration

Before automatic memory management begins, the appropriate settings for minimum and maximum memory must be applied. You should treat this as one of the steps to configure the SQL instance. The SQL script as below is only an example to use. You should automate it in the most convenient way for you. In our case, the script is part of the puppet module. The numbers below are only examples. You will have to come up with your own. Here you can read more about memory configuration.

EXEC sp_configure 'show',1;
reconfigure;
EXEC master..sp_configure 'min server memory (MB)',@configvalue=14336;
EXEC master..sp_configure 'max server memory (MB)',@configvalue=43008;
reconfigure;
SELECT GETDATE() AS DateChanged,
    @@servername AS ServerName,
    SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName,
    Name AS ConfigName,
    Value AS ConfigValue,
    value_in_use AS RunValue
FROM sys.configurations
WHERE name LIKE '%server memory (MB)'

Automatic memory management

In order to implement automatic memory management in FCI you will need two components.

• Powershell script adjusting maximum memory for the SQL instance in case of failover
• Windows schedule task that will trigger the powershell script in case of a specific event

Get-TargetMemory function

Firstly, You need to evaluate the amount of the host memory that can be dedicated to SQL Server. Assuming that there is nothing specific running on the host other than SQL server instances. The following Powershell function will be used for this purpose. This will ensure that the required memory is left for the operating system.

function Get-TargetMemory {

    $totalMemory = Get-WMIObject -class Win32_ComputerSystem | SELECT TotalPhysicalMemory 
    [int]$totalMemoryMB = $totalMemory.TotalPhysicalMemory / 1024 / 1024
    switch ($totalMemoryMB) { 
        { $_ -le 4096 } { $targetMemory = $totalMemoryMB - 1024 ; break }
        { $_ -le 8192 } { $targetMemory = $totalMemoryMB - 2048 ; break }
        { $_ -le 16384 } { $targetMemory = $totalMemoryMB - 3072 ; break }
        { $_ -le 32768 } { $targetMemory = $totalMemoryMB - 4096 ; break }
        { $_ -le 49152 } { $targetMemory = $totalMemoryMB - 6144 ; break }
        { $_ -le 65536 } { $targetMemory = $totalMemoryMB - 8192 ; break }
        default { $targetMemory = $totalMemoryMB - 10240 }
    }

    return $targetMemory

}

Set-TargetMemoryForSQLInstance function

Secondly, You will create a function that will adjust maximum memory setting for a single SQL instance. Select your favorite method to connect to a SQL Server instance and authenticate. I use a dedicated function that uses sql login authentication. This section has been removed to highlight the most important parts of the code.

function Set-TargetMemoryForSQLInstance {

    param (
        [string]$SQLInstance,
        [int]$TargetMemory
    )

    $query = "exec sp_configure 'show',1;reconfigure;exec master..sp_configure 'max server memory',@configvalue=$TargetMemory;reconfigure;"
    $result = <# Use your favourite method to run sql query#> -Query $query -Database "master" -Server $SQLInstance #Authentication parameters

}

Powershell main script

Thirdly, You will use both functions in the main script. Moreover, it will iterate through all nodes of the SQL cluster to estimate the number of SQL instances located on each of them. Then on each SQL instance, the maximum server memory will be set according to the equation: total target memory / number of SQL instances. Even though the script was triggered in case of single SQL instance failover, all SQL instances will be taken into account when setting the memory. In this way, you will ensure that all available memory in the entire SQL cluster will be appropriately distributed.

[int]$totalTargetMemory = Get-TargetMemory
$cluster = (hostname).Substring(0, 11)
$clusterNodes = Get-ClusterNode | where { $_.Name -like "$cluster*" }
  
foreach ( $clusterNode in $clusterNodes.Name ) {
  
    $SQLinstancesOnhost = Get-ClusterGroup -name "SQL Server*" |
                          where { $_.OwnerNode -like "*$clusterNode*" } |
                          Get-ClusterResource | where { $_.ResourceType -eq "SQL Server" } 
    [int]$SQLinstancescount = $SQLinstancesOnhost.count

    if ( $SQLinstancescount -gt 0 ) {
      
        $targetMemory = $totalTargetMemory / $SQLinstancescount

        foreach ( $SQLinstanceOnhost in $SQLinstancesOnhost ) {
  
            $clusterparams = $SQLinstanceOnhost | Get-ClusterParameter VirtualServerName, InstanceName
            $virtualServerName = ($clusterparams | where { $_.name -eq "VirtualServerName" }).value
            $instanceName = ($clusterparams | where { $_.name -eq "InstanceName" }).value
            $SqlInstance = ($virtualServerName + "\" + $instanceName).tolower()
            Set-TargetMemoryForSQLInstance -SQLInstance $SqlInstance -TargetMemory $targetMemory

        }

    }

}

The above script should be equipped with error handling as well as output logging. This section has been removed to highlight the most important parts of the code.

Windows scheduled task

Finally, You will need to configure the automatic trigger for the script. Windows scheduled task perfectly fits the purpose. Based on my experience trigger on EventID = 17126 – SQL Server is now ready for client connections works very well in this case. Below is an example of the xml export from scheduled task that I’ve been using to configure this to work. Just take it and import it in your environment. It assumes that powershell script is located in D:\Set-TargetMemoryForSQLInstances.ps1. It will be executed as NT AUTHORITY\SYSTEM user.

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.2" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Date>2019-04-16T17:31:44.6669173</Date>
  </RegistrationInfo>
  <Triggers>
    <EventTrigger>
      <Enabled>true</Enabled>
      <Subscription>&lt;QueryList&gt;&lt;Query Id="0" Path="Application"&gt;&lt;Select Path="Application"&gt;*[System[EventID=17126]]&lt;/Select&gt;&lt;/Query&gt;&lt;/QueryList&gt;</Subscription>
    </EventTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>S-1-5-18</UserId>
      <RunLevel>HighestAvailable</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>true</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>true</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>false</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>true</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>P3D</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>c:\windows\system32\WindowsPowerShell\v1.0\powershell.exe</Command>
      <Arguments>-executionpolicy bypass -file D:\Set-TargetMemoryForSQLInstances.ps1</Arguments>
    </Exec>
  </Actions>
</Task>

Another way to deal with a similar situation can be found here.

References:

• Always On Failover Cluster Instances (SQL Server)
• Memory Management Architecture Guide
• SQL Server Cluster Automatic Memory Management