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
