SQL Server Resource Re-Balancing in Failover Cluster

SQL Server Resource Re-Balancing in Failover Cluster


Table of Contents



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. 

See Also


Other Languages


Sort by: Published Date | Most Recent | Most Useful
Comments
Page 1 of 1 (1 items)