SQL Server Resource Re-Balancing in Failover Cluster
The poster asked how to automatically adjust SQL Server's max server memory setting following a cluster fail-over - see here. I provided the following script with suggestions for how it could be tailored for their environment.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster] Script Date: 05/13/2013 16:12:36 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Rebalance_RAM_in_Cluster]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
GO
USE [master]
GO
/****** Object: StoredProcedure [dbo].[usp_Rebalance_RAM_in_Cluster] Script Date: 05/13/2013 16:12:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_Rebalance_RAM_in_Cluster]
AS
--WAITFOR DELAY '00:00:15'
DECLARE @Command VARCHAR(2000)
DECLARE @RAM INT
DECLARE @RAM_event NVARCHAR(10)
DECLARE @Link VARCHAR(50)
DECLARE @RemSQL VARCHAR(500)
/*
AUTHOR: ANDREW BAINBRIDGE - MS SQL DBA
DATE: 13/05/2013
VERSION: 2.0
If there is a cluster failover that results in both SQL Server instances running on the same node, this script will
automatically rebalance the amount of RAM allocated to each instance. This is to prevent the combined RAM allocated to
SQL Server overwhelming the node.
If the D: drive and the H: drive are visible to the same host, that means that both
instances are running on the same node. In this event, the amount of RAM allocated to each of the SQL Servers
will be 90% of half the amount of total RAM in the server. E.g. (384GB / 2) * 0.9
If only the D: drive or H: drive is visible, then 90% of the total amount of RAM available on the server is allocated
to the SQL Server instance.
This stored procedure will also set the max server memory of the other SQL Server instance in the cluster. As this needs
to be run across the linked server, and the sp_procoption startup procedure is owned by SA (therefore can't use windows
authentication), the stored procedure will be run on SQL Server Agent startup, via a job.
*/
SET NOCOUNT ON;
BEGIN
IF (SELECT @@SERVERNAME) = 'MYSERVER\INSTANCE'
SET @Link = 'LINKED_SERVER_TO_OTHER_NODE'
ELSE
SET @Link = 'LINKED_SERVER_TO_MYSERVER\INSTANCE'
SET @Command = 'USE [master];
EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sp_configure ''max server memory (MB)'', $;
RECONFIGURE WITH OVERRIDE;'
IF OBJECT_ID('tempdb..#fd') IS NOT NULL
DROP TABLE #fd
CREATE TABLE #fd(drive CHAR(2), MBfree INT)
INSERT INTO #fd EXEC xp_fixeddrives
IF (SELECT COUNT(drive) FROM #fd WHERE drive IN ('D', 'H')) > 1
BEGIN
SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024) / 2) * 0.9) AS RAM_in_MB
FROM master.sys.dm_os_sys_info)
SET @Command = REPLACE(@Command, '$', @RAM)
SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
EXEC (@Command)
SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
EXEC (@RemSQL)
END
ELSE
BEGIN
SET @RAM = (SELECT CONVERT(INT, ((physical_memory_in_bytes / 1024 / 1024)) * 0.9) AS RAM_in_MB
FROM master.sys.dm_os_sys_info)
SET @Command = REPLACE(@Command, '$', @RAM)
SET @RAM_event = CONVERT(NVARCHAR(10), @RAM)
RAISERROR('MAX_SERVER_MEMORY set to %s', 0, 1, @RAM_event) WITH NOWAIT, LOG
EXEC(@Command)
SET @RemSQL = 'EXEC (''' + REPLACE(@Command, '''', '''''') + ''') AT ' + @Link
EXEC (@RemSQL)
END
END
GO
The script is executed via a SQL Server Agent job that is configured to run when the Agent service starts up.
This entry participated in the Technology Guru TechNet WiKi for May contest.