I was musing into a problem. We currently have our production server in a two node cluster that has 128GB of RAM each. 1st node contains 4 instances of SQL Server running, and the 2nd node contains 1 instance of SQL Server running. These are active/active clusters, meaning that in the event of failure, the other node takes on the processing, which ultimately degrades the performance. In the SQL Server running into 2nd node, we use some 84GB of memory and leave the remaining for failure events and the OS. In the 1st node though, we leave more than 90 GB for Node2 to be able to failover and not cause memory problems. I totally agree with the concept of high availability, but, for me, leaving so much amount of memory is just waste of resources.
We are adding additional node now, and the configuration will slightly change to active/active/passive clustering. I gathered my thoughts around the idea of writing a stored proc to manage the memory and do some math and decided to poke around with auto-memory management and startup stored procedure to achieve what I wanted to do so that when a fail over happens, and the SQL Server shifts to another node, the startup stored proc checks to see which node the server is on, and sets the right amount of memory.
Please do not use it in production without thoroughly verifying that this is not going to cause any problems. Startup stored procs has to be created on the master database (master is the first one which recovers after a restart), and cannot contain any input or output variables. There are other gotchas too.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54
create proc dbo.dba_startup as SET NOCOUNT ON; DECLARE @hostName varchar(100) DECLARE @tbl TABLE ( hostName varchar(100) ) INSERT INTO @tbl exec xp_cmdshell 'hostname' delete from @tbl where hostName is null select @hostname = hostname from @tbl --SELECT @hostName = HOST_NAME() if @hostName = 'Node1' BEGIN exec sys.sp_configure 'show advanced options', 1; RECONFIGURE EXEC sys.sp_configure 'max server memory', 20000; RECONFIGURE EXEC sys.sp_configure 'min server memory', 20000; RECONFIGURE EXEC sys.sp_configure 'show advanced options', 0; RECONFIGURE END if @hostName = 'Node2' begin exec sys.sp_configure 'show advanced options', 1; RECONFIGURE EXEC sys.sp_configure 'max server memory', 2147483647; RECONFIGURE EXEC sys.sp_configure 'min server memory', 0; RECONFIGURE EXEC sys.sp_configure 'show advanced options', 0; RECONFIGURE END SET NOCOUNT OFF; --Configuring this as startup stored proc exec dbo.sp_procoption N'dba_startup', 'startup', 'on'