Automatic change “Max Server Memory” value if a Failover occurs

For Spanish version visit this link Cambiar automáticamente el valor de “Max Server Memory” si ocurre un Failover
 
In one of our environments, we have a cluster with two active-active nodes with one SQL Server instance in each one.
During this week, a fail of the service occurred in one of the nodes, which caused a failover to the node that was available.
The problem that we found was that the node only have 25GB of RAM memory and the value of “Max Server Memory” assigned for each instance was 20GB, so we have the risk that if the two instances beginning to need more RAM memory would come a moment that the Windows OS would lose performance.
 
The idea is to keep always a minimum of 3GB of RAM memory available for Windows, so we need to create a script executed for a job that in case of two instances are in the same node, the value of “Max Server Memory” is reduced, and if they return to their normal state (one instance per node) the value is increased.
 
The first step is to create the Linked Server in each Instance (in both instances it is necessary to create the two Linked Server), once done, create a Job in each instance which will be responsible to check if the node have 1 or 2 instances, and if is necessary modify the “Max Server Memory” value.
 
--1. Create the Linked Server for each Instance (Required)
--Values to modify: @server, @datasrc
exec sp_addlinkedserver @server='ServerA', @srvproduct='', @provider='SQLNCLI', @datasrc='ServerA\InstanceA'
exec sp_addlinkedserver @server='ServerB', @srvproduct='', @provider='SQLNCLI', @datasrc='ServerB\InstanceB'

--2. Create a Job with this content for each Instance
--Values to modify: @memorynotshared, @memoryshared, ServerA, ServerB
DECLARE @memorynotshared as int
DECLARE @memoryshared as int
SET @memorynotshared = 20496 --Max Server Memory value when the Node has 1 Instance
SET @memoryshared = 10496 --Max Server Memory value when the Node has 2 Instances
IF (select * from openquery([ServerA], 'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')')) = (select * from openquery([ServerB], 'select SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'')'))
 BEGIN
 IF (select value from sys.configurations where name = 'max server memory (MB)') = @memorynotshared
 BEGIN
 exec sp_configure 'show advanced options', 1;
 RECONFIGURE;
 exec sp_configure 'max server memory', @memoryshared;
 RECONFIGURE;
 END
 END
ELSE
 BEGIN
 IF (select value from sys.configurations where name = 'max server memory (MB)') = @memoryshared
 BEGIN
 exec sp_configure 'show advanced options', 1;
 RECONFIGURE;
 exec sp_configure 'max server memory', @memorynotshared;
 RECONFIGURE;
 END
 END
 

About Fran Lens
SQL Server Analyst at Avanade Spain http://www.lensql.net

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

%d bloggers like this: