Dynamically Setting Max Server Memory on Cluster

I was recently given the task of adding a new node to our one node cluster with two SQL instances.  Due to politics within the company what was originally going to be a two-node SQL cluster had lingered around as a single node cluster for over a year. The issue I was confronted with was how to maximize memory usage on both nodes of the cluster but ensure that the two instances do not compete for memory if they happen to be running on the same node.

The solution I came up with was to query the server to determine the physical node name that each of the SQL instances was running on.  If after running both queries it is determined that the instances are running on the same node then the max server memory for both servers is reduced to prevent contention.  Conversely, if both instances are running on different nodes then the max server memory is increased to leave just enough for the operating system.  I remembered that changing the max server memory on an instance causes a dump of the procedure cache so I had to add logic to determine if the max server memory needed to be changed or if it could be left as it was.

There are several ways to get the code I created to run at startup.  One option is to create a stored procedure that runs the code and to have the stored procedure run at startup for the instance.  To execute the procedure this way the server must be configured to scan for startup procedures and you must run sp_procoption to set the stored procedure to run at startup. I chose to create a SQL Agent job to run the code and set the job schedule to run at agent startup to keep from having to reconfigure the server.  This way is also more transparent for any future DBAs.

In order to change the max server memory setting I had to create a linked server on both servers that I was sure would have ALTER SETTINGS permission.

Below is the code I created.

1:  DECLARE @standalonememory    int      = 125000  
2:  DECLARE @sharedmemory      int      = 55000  
3:  DECLARE @SQLRPTHost       nvarchar(50)  
4:  DECLARE @SQLDWHost        nvarchar(50)   
5:  DECLARE @SQLRPTmaxmemory     int   
6:  DECLARE @SQLDWmaxmemory     int   
7:     
8:  --Retrieve the host name for SQLRPT host    
9:  SELECT @SQLRPTHost = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS nvarchar(50))--Returns Node Name  
10:    
11:  -- Retrieve the host name for SQLDW      
12:  SELECT @SQLDWHost = ComputerNamePhysicalNetBIOS  
13:  FROM OPENQUERY (SYS_SQLDW,'SELECT CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS nvarchar(50)) AS ComputerNamePhysicalNetBIOS');  
14:    
15:  --Retrieve the max server memory on current server    
16:  SELECT @SQLRPTmaxmemory = CAST(value as int)     
17:  FROM sys.configurations    
18:  WHERE name = 'max server memory (MB)'   
19:    
20:  --Retrieve the max server memory on remote server    
21:  SELECT @SQLDWmaxmemory = value     
22:  FROM OPENQUERY(SYS_SQLDW, 'SELECT CAST(value as int) AS value  
23:      FROM sys.configurations  
24:      WHERE name = ''max server memory (MB)''')    
25:    
26:  IF @SQLRPTHost = @SQLDWHost      --Both instances running on same host      
27:   BEGIN        
28:      --Max Memory should be set to sharedmemory if not set it       
29:      IF @SQLRPTmaxmemory != @sharedmemory       
30:       BEGIN         
31:        
32:         EXEC sp_configure 'Max Server Memory', @sharedmemory         
33:         RECONFIGURE        
34:    
35:       END     --Check the memory setting for the SQLDW instance       
36:    
37:      IF @SQLDWmaxmemory != @sharedmemory  
38:       BEGIN         
39:       
40:         EXEC ('sp_configure ''Max Server Memory'', ' + @sharedmemory + '; RECONFIGURE') AT SYS_SQLDW        
41:     
42:       END        
43:    
44:      END     
45:         ELSE --Servers are each running on their own node      
46:          BEGIN         
47:            --Max Memory should be set to stand alone memory if not set it       
48:             IF @SQLRPTmaxmemory != @standalonememory       
49:    
50:              BEGIN         
51:                 
52:                EXEC sp_configure 'Max Server Memory', standalonememory         
53:                RECONFIGURE        
54:    
55:              END       
56:               
57:              --Check the memory setting for the SQLDW instance       
58:              IF @SQLDWmaxmemory != @standalonememory       
59:    
60:               BEGIN         
61:                    
62:                   EXEC ('sp_configure ''Max Server Memory'', ' + @standalonememory + '; RECONFIGURE') AT SYS_SQLDW        
63:                   
64:    
65:                END        
66:              END