First published on MSDN on Feb 19, 2013
Scenario: You have a two instance, two node cluster. SQL1 is typically running on Node1, SQL2 is typically running on Node2. The Objective is to maximize memory usage when each instance is running on its own node, yet balance memory usage if both of them happen to run on the same node.
You would like to run a script to reconfigure your SQL instances’ memory settings whenever a failover occurs (in order to automatically balance the memory resources) to account for the possibility of both instances running on the same node.
From BOL: Server Memory Options http://msdn.microsoft.com/en-us/library/ms178067(v=SQL.105).aspx
When you are running multiple instances of the Database Engine, there are three approaches you can use to manage memory:
-
Use max server memory to control memory usage. Establish maximum settings for each instance, being careful that the total allowance is not more than the total physical memory on your machine. You might want to give each instance memory proportional to its expected workload or database size. This approach has the advantage that when new processes or instances start up, free memory will be available to them immediately. The drawback is that if you are not running all of the instances, none of the running instances will be able to utilize the remaining free memory.
-
Use min server memory to control memory usage. Establish minimum settings for each instance, so that the sum of these minimums is 1-2 GB less than the total physical memory on your machine. Again, you may establish these minimums proportionately to the expected load of that instance. This approach has the advantage that if not all instances are running at the same time, the ones that are running can use the remaining free memory. This approach is also useful when there is another memory-intensive process on the computer, since it would insure that SQL Server would at least get a reasonable amount of memory. The drawback is that when a new instance (or any other process) starts, it may take some time for the running instances to release memory, especially if they must write modified pages back to their databases to do so. You may also need to increase the size of your paging file significantly.
-
Do nothing (not recommended). The first instances presented with a workload will tend to allocate all of memory. Idle instances or instances started later may end up running with only a minimal amount of memory available. SQL Server makes no attempt to balance memory usage across instances. All instances will, however, respond to Windows Memory Notification signals to adjust the size of their buffer pools. Windows does not balance memory across applications with the Memory Notification API. It merely provides global feedback as to the availability of memory on the system.
Potential Solution: There a number of ways to achieve this, we will use a PowerShell script that detects 2 instances of SQL on a failover cluster and balances memory between them if they happen to land on the same node. This script will be invoked as a SQLAgent Job on SQLAgent startup.
Assumption :
-
This will only work for a cluster with 2 instances of SQL Server on it.
-
If there are other applications clustered on the same cluster (i.e Analysis server), memory will still only be balanced between the 2 SQL instances ignoring other applications. You will have to modify this to work for a cluster with more than 2 instances.
Caveats:
-
If you drastically reduce memory of an instance, it could cause out of memory errors, this is whether you do it “manually” or automatically. So if you drop an instance that was prior using 28GB to 14GB, that can have ramifications on that instance.
-
If you use LARGE PAGE memory model, memory allocation is at Startup time only, so in that case you cannot use this approach and have to live with leaving enough free to accommodate the other instance sharing this node. You can incorporate that check in the script itself.
-
The Powershell Cmdlets are available on Windows 2008 R2 onwards
Note : I am not a PowerShell expert by any means, and am sure there are many ways to improve on the script; this is intended more as a sample.
Let’s walk through the PowerShell script so that I can point out some Key parts.
a. Configure the Log file location – the path to the log file needs to be on a shared drive that is within the SQL Server Application group or a local drive that exists on both nodes as it needs to be accessible from both instances. This log file should have the date and time that the script was run along with what it configured memory to be at the end of the run.
$LogFile="R:\Temp\SetMemoryUsageConfig.out"
b. We then get the SQL instances and currently if there are more than 2 instances this script will do nothing.
$SQLRes=Get-ClusterResource | where-object {$_.ResourceType -ilike "SQL Server" }
c.If there are 2 instances we then invoke ManageResourceUsage function that does 2 things
i. If the 2 instances are running on the same node and are online, it calls SetBalancedResourceUsage
ii. If the instance moves to a node by itself it calls SetStandardResourceUsage
d.On a failover, the script connects to “both” instances and configures their memory appropriately which is done through the calls
SetBalancedResourceUsageConfig $vs1 $inst1 SetBalancedResourceUsageConfig $vs2 $inst2
OR
SetStandardResourceUsageConfig $vs2 $inst2 SetStandardResourceUsageConfig $vs1 $inst1
e.Both SetBalancedResourceUsage and SetStandardResourceUsage make a call to the function “ CalculateSQLTargetMemoryCombined ” which effectively is a routine you “may” want to change depending on your configuration
Note : I have a minimalistic approach in terms of the algorithm, for critical systems you will have to do a better assessment as to what memory configuration should be.
My minimalistic algorithm here takes the Physical memory on the node, and it goes through a blind Case statement which states that if 4GB, leave 1GB for the OS, if 8GB, leave 2 GB for the OS, if 16GB, leave 3GB for the OS, if 32GB, leave 4GB for the OS.
In order to determine actual usage, check the article - http://support.microsoft.com/kb/918483
Section : “How to determine the memory that is used by 64-bit editions of SQL Server “
This returns a Combined Target which is then “shared” if the instances are on the same node, or configured for the instance itself if alone on its own node.
$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- 3096 ;break}
{$_ -le 32768} {$TargetMemory=$TotalMemoryMB- 4192 ;break}
{$_ -le 65536} {$TargetMemory=$TotalMemoryMB- 8384 ;break}
default {$TargetMemory=$TotalMemoryMB- 10240}
}
How to Configure the Script to run – Test on a Test Server
a. Drop the PowerShell script attached – Online.ps1 into a folder on a shared drive folder OR on a local path that exists on both the nodes in the cluster, the objective is that instance should be able to access it from both nodes.
Example: R:\Temp
b. Configure the log, ideally to the same location created above
$LogFile="R:\Temp\SetStandardResourceUsageConfig.out"
c. Configure a Job in SQL Agent ( required for “each” of the 2 instances on the cluster). Ensure that the Job is setup to Start when SQLAgent Starts. We are using a CmdExec Job because in SQL 2008, the powershell job subsystem ( sqlps) does not support Import-Module. That should work on SQL 2012 though.
d. Failover the Node in order to test
e.After the Failover, check the Log file configured, and you should see the output such as below
Both instances on the same Node after failover:
DateChanged : 1/7/2013 12:00:52 PM ServerName : SQL2749961\MSSQLSERVER1 NodeName : DENZILR221 ConfigName : max server memory (MB) ConfigValue : 1536 RunValue : 1536 DateChanged : 1/7/2013 12:00:53 PM ServerName : SQL2749962\MSSQLSERVER2 NodeName : DENZILR221 ConfigName : max server memory (MB) ConfigValue : 1536 RunValue : 1536Each instance on its own node after failover
DateChanged : 1/7/2013 12:02:59 PM ServerName : SQL2749962\MSSQLSERVER2 NodeName : DENZILR221 ConfigName : max server memory (MB) ConfigValue : 3072 RunValue : 3072 DateChanged : 1/7/2013 12:02:59 PM ServerName : SQL2749961\MSSQLSERVER1 NodeName : DENZILR24 ConfigName : max server memory (MB) ConfigValue : 3072 RunValue : 3072
Disclaimer : The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages.
Denzil Ribeiro & Fany Vargas
Senior Premier Field Engineers