Let me begin this article by giving proper credit to the source of my information. The system described below comes directly from a blog titled “Tom’s Blog – a journey into the SQL world”. The system described is located in the post: SQL Clustered Instances automatic Max Server Memory settings. My intent is not to pass this along as my own but, since it was originally posted in 2011, I believe this is good material and wanted to get this back out into the SQL Server world. I am also adding more details to the original article to provide additional insight into the system. All of the code is included as an attachment to this article.

One problem with SQL Server clusters involves memory management. Ideally, each node in a SQL Server cluster would host a single SQL Server instance. In this scenario, each instance could be set up to utilize all of the memory on the individual nodes. There are times, however, when a node might have to host multiple instances; a down server, maintenance, simply more instances than nodes, or other issues. In these cases, each SQL Server instance needs to share the resources on the node on which they reside. Most resources are handled automatically but memory needs to be adjusted accordingly.

You don’t want the total memory allocated to all the instances on a single node to be greater than the total memory on the node. Most DBAs allocate memory so that if all instances are sharing one server, the total memory allocation remains below the total server memory. While allocating more memory than the server’s maximum can work, you could run into problems. On the other hand, when the instances are running in the ideal situation, one instance per node, these lower allocations mean that you are potentially wasting a lot of memory on each individual node.

To address this situation, the system described below will automatically adjust the MAX MEMORY setting for all SQL Server instances in a cluster any time an instance fails over to a different node. This ensures that when a single node is hosting more than one instance, memory is fully allocated but never over-allocated. This also means that when there is a single node on a single instance, that instance can use all of the memory available on that node.

This solution is accomplished by assigning a “weight” to each SQL Server instance on the cluster. These “weights” are later used to calculate a factor, or percentage. When multiple instances are sharing a node, each instance’s “weight” is used to calculate a percentage and that percentage is used to set the amount of memory allocated to that instance. These weights are maintained in a table on the “master” instance (more on that later), along with the current node on which each instance resides. This is how the process knows how to divide the memory. The following is an example:

Say we have a three node cluster, with three SQL Server instances. The nodes are N1, N2, and N3. The SQL Server instances are SQLA, SQLB, and SQLC. For this example, assume each node has 250,000 MB of memory. Assuming an ideal configuration, the “weights” table would look like the following:

In this case, each instance would get 244,000 MB of memory (this assumes 6,000 MB of memory left for the OS).

Now, assume that SQLB failed over to node N1. The “weights” table would be updated to look like the following:

In this case, SQLC would still have 244,000 MB of memory on node N3. On node N1, however, SQLA would now have, approximately, 132,980 MB of memory and SQLB would have, approximately, 110,020 MB of memory. The calculations for this are as follows:

Factor:  InstanceWeight / SumOfAllInstanceWeightsOnNode

Max Memory:  (MemoryInNode – MemoryForOS) * Factor

SQLA:            Factor = 30/55 = 0.545

                        Max Memory = (250,000 – 6,000) * 0.545 = 132,980

SQLB:             Factor = 25/55 = 0.455

                        Max Memory = (250,000 - 6,000) * 0.455 = 110,020

Setup

Earlier, I mentioned that these weights are maintained in a “weights” table on the “master” instance. The “master” instance is simply a SQL Server instance on the cluster that you choose to be the “master”. Because this could change, if you ever need to identify or confirm the “master” instance, the best idea is to search for the “master” instance in a cluster. This can be done in a couple of ways. One, you can search for the dbo.Memory_Settings table in the master database on each instance in the cluster. There should be only one. If so, that is the master instance. If you find more than one, use the second option. Check the code in the stored procedures to verify which instance is being referenced.For our example, we will use SQLA as our “master” instance.

Since there is no way to automate the keeping of the “master” instance, and its associated table and references, it must be done manually. If there is ever any question, the settings should be verified.The code for the “weights” table is as follows:

USE [master]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Memory_Settings]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Memory_Settings](
       [Instance_Name] [varchar](256) NOT NULL,
       [Active_Node_Name] [varchar](256) NOT NULL,
       [Weight] [int] NOT NULL,
 CONSTRAINT [PK_MemorySettings] PRIMARY KEY CLUSTERED
(
       [Instance_Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO

Now populate the Memory_Settings table as previously identified with the instance names, active nodes, and each instance’s weight.

The next step is to create a linked server, on each instance, to the other instances in the cluster. This must be done to allow the sp_Memory_Settings stored procedures to be called on each instance to set the new MAX MEMORY value for each instance.

Next, create the sp_Detect_Instance_And_Node_Settings stored procedure in the master database on each instance. This procedure will update the Memory_Settings table and call the sp_Memory_Settings stored procedure on each instance.

The original article said to set this stored procedure to execute on SQL service start but we had some issues where the procedure would not run properly because it would sometimes start before all the instances were completely ready for processing. To alleviate this situation, we set up a SQL Server Agent job that starts on SQL Server Agent startup, to call this procedure. This has worked well for us. You can choose whichever option you prefer. If you choose to set the procedure to run directly at startup, use the sp_procoption to do so.

Here is the code for the sp_Detect_Instance_And_Node_Settings procedure:

USE [master]
GO
CREATE PROC [dbo].[sp_Detect_Instance_And_Node_Settings]
AS
/*
    Purpose:    This procedure takes each instance, and the current node it is running
                           on, and places that data into the "master" config table (dbo.Memory_Settings).
                           This procedure will be set as AutoExecute on the startup of each instance.
                           The last step will be a cursor to call sp_Set_Memory_Settings on each
                           instance to set the MAX memory appropriately on each node, based on
                           the number of instances on that node and their weight.
    PreReq:     Table “Memory_Settings” on a “master” instance
    Version:    0.1 Initial Version
    Author:     Tom Van Zele (tvz@live.be)  http://synsol.eu/blog/2011/03/sql-clustered-instances-automatic-max-server-memory-settings/
*/DECLARE @Active_Node_Name         varchar(256),
        @Instance_Name            varchar(256),
        @Instance_Name_To_Check   varchar(256),
        @SQL                      varchar(1000)
/* Step 1: Update the “master” table with the ActiveNodeName */SET @Instance_Name = @@SERVERNAME
SET @Active_Node_Name = CONVERT(varchar(256), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
UPDATE [SQLA].master.dbo.Memory_Settings /* Replace [SQLA] with the name of your “master” instance */SET Active_Node_Name = @Active_Node_Name
WHERE Instance_Name = @Instance_Name
/* Step 2: call all registered instances to execute sp_SetMemorySettings in order to set their MaxMemory */DECLARE Instance_Cursor CURSOR FOR
SELECT DISTINCT Instance_Name
FROM [SQLA].master.dbo.Memory_Settings  /* Replace [SQLA] with the name of your “master” instance */ORDER BY Instance_Name
OPEN Instance_Cursor
FETCH NEXT FROM Instance_Cursor INTO @Instance_Name_To_Check
WHILE @@FETCH_STATUS = 0
BEGIN
       SET @SQL = 'EXEC [' + @Instance_Name_To_Check + '].master.dbo.sp_Memory_Settings'
       PRINT (@SQL)
       EXEC (@SQL)
       FETCH NEXT FROM Instance_Cursor INTO @Instance_Name_To_Check
END
CLOSE Instance_Cursor
DEALLOCATE Instance_Cursor
GO

The next step is to create the sp_Memory_Settings stored procedure in the master database on each instance. This procedure performs the calculations to determine the correct memory setting for each instance on that node and set the maximum memory. The code for this procedure is as follows:

USE [master]
GO
CREATE PROC [dbo].[sp_Memory_Settings]
AS
/*
    Purpose:    This procedure will adjust memory Settings based on the “master” instance input
    PreReq:     Table “Memory_Settings” on a “master” instance
    Version:    0.1 Initial Version
    Author:     Tom Van Zele (tvz@live.be)  http://synsol.eu/blog/2011/03/sql-clustered-instances-automatic-max-server-memory-settings/
*/DECLARE @Active_Node_Name                 varchar(256),
        @Factor                           dec(10,2),
        @Instance_Name                    varchar(256),
        @Max_Memory                       int,
        @Memory_In_Node                   int,
        @Reserved_For_System              int,
        @Sum_Of_Weight_On_Active_Node     dec(10,2),
        @Weight                           dec(10,2)
/* Step 1: Calculate our max memory setting
       Enter as precise MB to get GB:
              2048 MB = 2 GB
              4096 MB = 4 GB
              6144 MB = 6 GB
              8192 MB = 8 GB
*/SET @Reserved_For_System = 6144 -- Reserved memory for system, change as you like
SET @Instance_Name = @@SERVERNAME
SET @Active_Node_Name = CONVERT(varchar(256), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
SELECT @Weight = [Weight]
FROM [SQLA].master.dbo.Memory_Settings /* Replace [SQLA] with the name of your “master” instance */WHERE Instance_Name = @Instance_Name
-- print @Weight
SELECT @Sum_Of_Weight_On_Active_Node = SUM(Weight)
FROM [SQLA].master.dbo.Memory_Settings /* Replace [SQLA] with the name of your “master” instance */WHERE Active_Node_Name = @Active_Node_Name
-- print @Sum_Of_Weight_On_Active_Node
SELECT @Factor = @Weight/@Sum_Of_Weight_On_Active_Node
-- print (@Factor)
SELECT @Memory_In_Node = physical_memory_kb/1024
FROM sys.dm_os_sys_info
-- print @Memory_In_Node
SELECT @Max_Memory = (@Memory_In_Node - @Reserved_For_System) * @Factor
-- print @Max_Memory
/* Step 2: SET our calculated MaxMemory value */EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
EXEC sp_configure 'max server memory (MB)', @Max_Memory
RECONFIGURE
EXEC sp_configure 'show advanced option', '0'
RECONFIGURE
GO

The final piece to this system is the SQL Server Agent job that makes everything run. For this example, the job is called Cluster – Set Max Memory. It runs every time the SQL Server Agent starts, which would be during any restart, whether caused by a failover or not. If it is not a failover, it doesn’t hurt anything for the job to run. The only time this would not run is if the SQL Server Agent fails to start after a failover.

VERY IMPORTANT:  If you choose to use the SQL Server Agent method for starting the job, you will need to make sure some proper permissions are set up in each instance to make this work. You will either have to run the SQL Server Agent job step as an account that has sysadmin rights on each instance or you will need to grant the SQL Server Agent service account sysadmin rights on each of the other instances in the cluster. These high levels of access are needed because you are changing the maximum memory setting, using sp_configure, through a stored procedure that is being run from another server.

Here is the code for this job:

USE [msdb]
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
select @jobId = job_id from msdb.dbo.sysjobs where (name = N'Cluster - Set Max Memory')
if (@jobId is NULL)
BEGIN
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Cluster - Set Max Memory',
              @enabled=1,
              @notify_level_eventlog=0,
              @notify_level_email=2,
              @notify_level_netsend=0,
              @notify_level_page=0,
              @delete_level=0,
              @description=N'This job will run on the SQL Server Agent startup and it will set all of the Max Memory settings for each server in the cluster, based on weights and how many instances are running on each node.  Check the procedures for more information.',
              @category_name=N'[Uncategorized (Local)]',
              @owner_login_name=N'DOMAIN\svc_acct',
              @notify_email_operator_name=N'SQL Administrators', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobsteps WHERE job_id = @jobId and step_id = 1)
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Set Max Memory',
              @step_id=1,
              @cmdexec_success_code=0,
              @on_success_action=1,
              @on_success_step_id=0,
              @on_fail_action=2,
              @on_fail_step_id=0,
              @retry_attempts=0,
              @retry_interval=0,
              @os_run_priority=0, @subsystem=N'TSQL',
              @command=N'EXEC dbo.sp_Detect_Instance_And_Node_Settings',
              @database_name=N'master',
              @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Set Max Memory',
              @enabled=1,
              @freq_type=64,
              @freq_interval=0,
              @freq_subday_type=0,
              @freq_subday_interval=0,
              @freq_relative_interval=0,
              @freq_recurrence_factor=0,
              @active_start_date=20160914,
              @active_end_date=99991231,
              @active_start_time=0,
              @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

I hope you find this article helpful. Please let me know if you have any questions, or any suggestions for making this system better.