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.
DECLARE @standalonememory int = 125000
DECLARE @sharedmemory int = 55000
DECLARE @SQLRPTHost nvarchar(50)
DECLARE @SQLDWHost nvarchar(50)
DECLARE @SQLRPTmaxmemory int
DECLARE @SQLDWmaxmemory int
--Retrieve the host name for SQLRPT host
SELECT @SQLRPTHost = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS nvarchar(50))--Returns Node Name
-- Retrieve the host name for SQLDW
SELECT @SQLDWHost = ComputerNamePhysicalNetBIOS
FROM OPENQUERY (SYS_SQLDW,'SELECT CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS nvarchar(50)) AS ComputerNamePhysicalNetBIOS');
--Retrieve the max server memory on current server
SELECT @SQLRPTmaxmemory = CAST(value as int)
FROM sys.configurations
WHERE name = 'max server memory (MB)'
--Retrieve the max server memory on remote server
SELECT @SQLDWmaxmemory = value
FROM OPENQUERY(SYS_SQLDW, 'SELECT CAST(value as int) AS value
FROM sys.configurations
WHERE name = ''max server memory (MB)''')
IF @SQLRPTHost = @SQLDWHost --Both instances running on same host
BEGIN
--Max Memory should be set to sharedmemory if not set it
IF @SQLRPTmaxmemory != @sharedmemory
BEGIN
EXEC sp_configure 'Max Server Memory', @sharedmemory
RECONFIGURE
END --Check the memory setting for the SQLDW instance
IF @SQLDWmaxmemory != @sharedmemory
BEGIN
EXEC ('sp_configure ''Max Server Memory'', ' + @sharedmemory + '; RECONFIGURE') AT SYS_SQLDW
END
END
ELSE --Servers are each running on their own node
BEGIN
--Max Memory should be set to stand alone memory if not set it
IF @SQLRPTmaxmemory != @standalonememory
BEGIN
EXEC sp_configure 'Max Server Memory', standalonememory
RECONFIGURE
END
--Check the memory setting for the SQLDW instance
IF @SQLDWmaxmemory != @standalonememory
BEGIN
EXEC ('sp_configure ''Max Server Memory'', ' + @standalonememory + '; RECONFIGURE') AT SYS_SQLDW
END
END
The job is then basically duplicated on the second SQL instance with the servers being reversed so SQLRPT becomes the remote server and SQLDW becomes the local server.
