Memory management
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, everyone should pay special attention to automatic memory management. Here You can read more about FCI.
Initial memory configuration
In order to use the automatic memory management, You must specify the appropriate settings for minimum and maximum memory. 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. We will use the following Powershell function 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. I removed this section 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. I removed this section 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><QueryList><Query Id="0" Path="Application"><Select Path="Application">*[System[EventID=17126]]</Select></Query></QueryList></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>
here You can find another way to deal with similar situation.