In our environment we have several SQL clusters, most of them in an active-active configuration.

Microsoft recommends that the sum of all maximum server memory for all instances is equal to the total installed memory – memory reserved for the system. This means that during normal circumstances, more than half of the memory is not used. While I can understand that Microsoft recommends this out of safety, I think it’s stupid to not let SQL profit from extra memory that is ready to be used.

I found some references to Memory Rebalance scripts on the net, but found them to be useless for me, as they either used a default node, were limited to 2 instances or required the nodes to have the same amount of memory installed.

Therefore, I have created a solution that uses ‘weights’ to calculate the maximum server memory for a given instance.

How it works:

  • I select one instance to be the ‘master’ instance. In the master database of this instance, I create a new table which contains the InstanceNames, NodeName on which this instance is currently active and the weight.
    Script:

    USE [master]
    GO

    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[MemorySettings]‘) AND type in (N’U'))
    BEGIN
    CREATE TABLE [dbo].[MemorySettings](
        [InstanceName] [varchar](256) NOT NULL,
        [ActiveNodeName] [varchar](256) NOT NULL,
        [Weight] [int] NOT NULL
    ) ON [PRIMARY]
    END
    GO
  • I fill in the InstanceNames, current Active Nodenames and weights in this table, for example:
    ExampleOfInput 
  • Now create on each instance a linked server to the other instances in the cluster.
  • Next I create a stored procedure in each master database. This stored procedure will be set to execute on SQL service start. It will update the MemorySettings database on the master instance and call the sp_MemorySettings procedure on each instance. 

    create proc dbo.sp_DetectInstanceAndNodeSettings
    as
    /*
        Purpose:    This procedure takes the instancename and current running node and places it in
                    the “master” config table.
                    This procedure will be set as autoexecute on startup of each instance.
                    The latest step will be a cursor to call sp_SetMemorySettings on each instance
        PreReq:        Table “MemorySettings” on a “master” instance
        Version:    0.1 Initial Version
        Author:        Tom Van Zele (tvz@live.be)
    */

    /* Step 1: Update the “master” table with the ActiveNodeName */

    declare @InstanceName as varchar(256)
    declare @ActiveNodeName as varchar(256)

    set @InstanceName = (select @@SERVERNAME)
    set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256)))

    UPDATE [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */
    SET ActiveNodeName = @ActiveNodeName
    WHERE InstanceName = @InstanceName

    /* Step 2: call all registered instances to execute sp_SetMemorySettings in order to set their MaxMemory */
    declare @SQLToExecute varchar(1000)
    declare @InstanceNameToCheck varchar(256)

    declare AllInstancesCursor CURSOR for select distinct InstanceName from [YourMasterInstance].master.dbo.MemorySettings /* Replace [YourMasterInstance] with the name of your “master” instance */

    open AllInstancesCursor
    fetch next from AllInstancesCursor into @InstanceNameToCheck
    while @@FETCH_STATUS=0
    begin
        set @SQLToExecute = ‘EXEC [' + @InstanceNameToCheck + '].master.dbo.sp_MemorySettings’
        print (@SQLToExecute)
        exec (@SQLToExecute)
        fetch next from AllInstancesCursor into @InstanceNameToCheck
    end
    close AllInstancesCursor
    deallocate AllInstancesCursor

    GO

      exec sp_procoption @ProcName=[‘sp_DetectInstanceAndNodeSettings’], @OptionName=‘STARTUP’, @OptionValue = [on]
    GO
  • Then finally we create the sp_MemorySetting stored procedure in the master database of each instance in the cluster. This proc will check the current ActiveNode, detect what other instances are active on the same node and will calculate and set the Maximum Server Memory, based on the weights.
     

    • create proc dbo.sp_MemorySettings
    as
    /*
        Purpose:    This procedure will adjust memory settings based on the “master” instance input
        PreReq:        Table “MemorySettings” on a “master” instance
        Version:    0.1 Initial Version
        Author:        Tom Van Zele (tvz@live.be)
    */

    /* Step 1: Calculate our max memory setting */
    declare @InstanceName as varchar(256)
    declare @ActiveNodeName as varchar(256)
    declare @Weight as dec(10,2)
    declare @SumOfWeightOnActiveNode as dec(10,2)
    declare @Factor as dec(10,2)
    declare @MemoryInNode as int
    declare @MaxMemory as int
    declare @ReservedForSystem as int 
    set @ReservedForSystem = 2048 — Reserved memory for system, change as you like

    set @InstanceName = (select @@SERVERNAME)
    set @ActiveNodeName = (select CAST(SERVERPROPERTY(‘ComputerNamePhysicalNetBIOS’) as varchar(256)))

    select @Weight =  [Weight] from [YourMasterInstance].master.dbo.MemorySettings where InstanceName = @InstanceName

    /* Replace [YourMasterInstance] with the name of your “master” instance */
    – print @Weight
    select @SumOfWeightOnActiveNode = SUM(Weight) from [YourMasterInstance].master.dbo.MemorySettings where ActiveNodeName=@ActiveNodeName

    /* Replace [YourMasterInstance] with the name of your “master” instance */

    – print @SumOfWeightOnActiveNode
    select @Factor = @Weight/@SumOfWeightOnActiveNode
    – print (@Factor)

    select @MemoryInNode = physical_memory_in_bytes/1048576 from sys.dm_os_sys_info
    – print @MemoryInNode

    select @MaxMemory = (@MemoryInNode-@ReservedForSystem)*@Factor
    – print @MaxMemory

    /* Step 2: Set our calculated MaxMemory value */
    exec sp_configure ‘show advanced option’,’1′;
    RECONFIGURE;
    exec sp_configure ‘max server memory (MB)’,@MaxMemory
    RECONFIGURE;
    exec sp_configure ‘show advanced option’,’0′;
    RECONFIGURE;

    GO

 

Known issue:

If you have a SQL 2008, a linked server is not automatically configured to accept RPC (You get an error that Server [servername] is not configured for RPC). To change this, please execute the following:

exec sp_serveroption @server=[YourInstanceName], @optname=’rpc’, @optvalue=’true’
exec sp_serveroption @server=[YourInstanceName], @optname=’rpc out’, @optvalue=’true’

So far the script works fine for me. If you have any remarks/better ideas, feel free to add a comment!

Tom

Bookmark and Share