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'