Category: SQL Server

by Fran Lens 2011-11-17

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

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