SQL Server failover clustering is widely implemented in the world. One of the DBA’s tasks is to adjust memory when failover happens. When that happens, 2 or more SQL instances are running on one physical node, then max memory for both instances should be adjusted. When the failed nodes are up, failback will perform, then DBAs need to do memory adjustments again on all affected instances. It would be more productive if this process can be automated. In this post, I will give you my ideas and the implementation of it.
The idea is that
- A process reads the location of the instance from the cluster
- Once the instance distribution is confirmed, this process calls a function to return the memory configuration for that distribution
- The process uses the memory configurations returned by the function to adjust instances in the cluster.
A table needs to be created to host the node information. MaxMemory is the maximum memory can be assigned to all the SQL Server instances on this node because we always want to keep some memory for the OS.
create table dbo.ClusterPhysicalNode ( NodeName varchar(128) primary key, MaxMemory int not null default(0) ) --This table can be populated from DMV sys.dm_os_cluster_nodes
Following code can return physical node name from SQL Server instance
select cast(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') as varchar(128)), cast(@@ServerName as varchar(128))
sp_configure can be used to ajust memory settings for each instance.
Now all technical difficulties and exisitng known configurations are clear. The problem becomes to how to calculate the memory distribution. I create a table to give every SQL instance a “weight” value and based on different weights to calculate the memeory configuration under various situations.
create table dbo.ClusterSQLNode ( NodeName varchar(128) primary key, Weight int not null default(0) )
Let’s say you have 3 active-active cluster, 3 SQL Server instances, S1, S2, and S3 with 3 Nodes, N1, N2, and N3. Every node has 32 GB memory but maximum allowable memory for SQL Server is 28GB, 28672 MB. In ClusterPhysicalNode table, I will put 28672 into MaxMemory for each node. Assuming you want to give S1 55% of memory, S2 25% of memory, and S3 20% of memory when those 3 are running on one node. I will put (S1,55), (S2, 25), (S3, 20) in table ClusterSQLNode table. The calculation of memory configuations on a node will be
Allowable memory * Weight of SQL Node / Total weight of all SQL Instances on the node.
Let’s say in the normal case S1 is running on N1. S1 should get memory configuration 28672 * 55 / 55. ( the First 55 is the weight of the S1, the second 55 is the total weight of all SQL instances on N1). So, S1 gets 100% of allowable memroy on node N1. Assuming failover happens, N1 now has S1 and S3. Memory settings for S1 will become to 28672 * 55 / (55 + 20) = 21026 MB. Memory settings for S3 will be 28672 * 20 / (55 + 20) = 7646 MB. This seems work. Now let’s implement it.
First, I think DBAs will be interested to know and test the weights for different failover scenarios. I create a function for this purpose as well as the process itself.
create type dbo.ClusterNodeDistribution as table ( InstanceName varchar(128) primary key, NodeName varchar(128) NOT NULL, unique(NodeName, InstanceName) -- this is not necessary, just for index purpose. ) go create function dbo.ClusterMemoryDistribution(@ClusterNodeDistribution ClusterNodeDistribution readonly) returns @ret table (InstanceName varchar(128), Memory int) as begin insert into @ret(InstanceName, Memory) select InstanceName, cast(( (select b.MaxMemory from ClusterPhysicalNode b where b.NodeName = a.NodeName) *(select c.Weight from ClusterSQLNode c where c.NodeName = a.InstanceName) * 1.0 /( select sum(d.Weight) from ClusterSQLNode d where NodeName in ( select InstanceName from @ClusterNodeDistribution e where e.NodeName = a.NodeName ) ) ) as int) from @ClusterNodeDistribution a return end go
Ok, now it’s time to implement the process
create procedure dbo.ClusterSetAll @Retry int = 10, @debug int = 0 as begin set xact_abort off declare @ClusterNodeDistribution ClusterNodeDistribution declare @InstanceName nvarchar(max), @Proc sysname, @Errors varchar(max) = '', @MaxMemory int, @SQL nvarchar(max), @CurrentMemory int, @Executions int = 0 while @Executions < @Retry begin begin try declare c cursor local static for select NodeName from ClusterSQLNode open c fetch next from c into @InstanceName while @@FETCH_STATUS = 0 begin begin try select @Proc = QUOTENAME(@InstanceName)+'.master..sp_executesql' insert into @ClusterNodeDistribution exec @Proc N'select cast(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') as varchar(128)), cast(@@ServerName as varchar(128))' end try begin catch select @Errors = @Errors + ERROR_MESSAGE() end catch fetch next from c into @InstanceName end close c select @SQL = ' select @CurrentMemory = cast(value as int) from sys.configurations where name = ''max server memory (MB)'' if @CurrentMemory!= @MaxMemory and @Debug = 0 begin exec sp_configure ''show advanced options'', 1 reconfigure with override exec sp_configure ''min server memory (MB)'', 0 exec sp_configure ''max server memory (MB)'', @MaxMemory exec sp_configure ''show advanced options'', 0 reconfigure with override end ' deallocate c declare c cursor local static for select InstanceName, Memory from dbo.ClusterMemoryDistribution(@ClusterNodeDistribution) a open c fetch next from c into @InstanceName, @MaxMemory while @@FETCH_STATUS = 0 begin select @Proc = QUOTENAME(@InstanceName)+'.master..sp_executesql' exec @Proc @SQL, N'@MaxMemory int, @CurrentMemory int output, @Debug int', @MaxMemory, @CurrentMemory output, @debug if @debug !=0 print 'Memory on ' + @InstanceName + ' will be adjusted from ' + cast(@CurrentMemory as varchar(20)) + ' to ' + cast(@MaxMemory as varchar(20)) fetch next from c into @InstanceName, @MaxMemory end close c deallocate c return end try begin catch print 'Execution ' + cast(@Executions as varchar(20)) + ' is failed.' print error_message() select @Executions = @Executions + 1 end catch end end
This procedure should be scheduled when SQL Server Agent starts and run once every 10 minutes or 20 minutes. It will only adjust the memory settings when it's needed.
Hi – I wrote a similar procedure a while back, which might be interesting. It doesn’t have the weighting that you incorporated, but it does have the advantage of only having to run on agent start, not other times: http://sqlblog.com/blogs/merrill_aldrich/archive/2010/01/22/auto-tuning-memory-configuration-on-a-cluster.aspx
Thanks for visiting my blog. I thought about running it only once when AgentStart but I figured that it would not hurt even run it periodically. The good thing about it is that if one fails or anyting happens which potentially cause any erroneous statuses, we know that they will be corrected in next execution (shortly). 🙂
Hi John
Wonderful post.This is what I was exactly looking for .I did not test it out at my test system , but , wanted to reach out and ask you before I try . Does your solution works on a 4 node cluster where 4th node is the passive node for the other 3 active nodes ?
Totally forgot…. you might have to read the code through…