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
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 (email@example.com) 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 (firstname.lastname@example.org) 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.