SQL Clustered Instances automatic Max Server Memory settings

By tom on March 15th, 2011

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:
     
  • 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

Clustered SQL Server 2008 SP1: services not starting

By tom on December 15th, 2009

I ran into a problem last week with our design SQL 2008 cluster. After finally having our instances installed (were we had some problems with our HP CLX component – you can read about this here) we noticed that the SQL Server and SQL Server Agent services failed.

We also noticed following entries in the SQL Server Errorlogs:

Logon       Error: 17806, Severity: 20, State: 2.
Logon       SSPI handshake failed with error code 0x8009030c while establishing a connection with integrated security; the connection has been closed. [CLIENT: 171.26.245.106]
Logon       Error: 18452, Severity: 14, State: 1.
Logon       Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 171.26.245.106]

The SSPI handshake error sounds like a kerberos problem, so I checked the SPNs (they were correctly registered) and the kerberos tickets (with kerbtray). No problems there at first sight.

Starting the SQL Server services from the local services window went fine, no errors in the eventlog/SQL errorlog. However in SQL Server Management Studio when trying to log on with windows authentication, it gave me the same error I saw in the errorlog: “Login failed. The login is from an untrusted domain and cannot be used with Windows authentication”. Logging on with a SQL account works fine.
Sounds like an authentication issue…

With the help of Microsoft Support we found that KB957097 was the culprit. This is a security fix to avoid remote code execution. There are 2 workarounds given in the KB. The first simply disables the LoopbackDetectionCheck. This might nog be a good idea, as this makes your system vulnerable again for the remode code execution flaw.
The second one seems to be the good one:
HKLM\SYSTEM\CurrentControlSet\Control\Lsa –> new DWORD DisableLoopbackCheck = 0
HKLM\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 –> new Multi-String BackConnectionHostNames = CNAME of your server

Apply this on both nodes of your cluster, reboot and problem solved.

Update:
MS Support gave me a little bit more info on this:

This issue occurs if you install Microsoft Windows XP Service Pack 2 (SP2) or Microsoft Windows Server 2003 Service Pack 1 (SP1). Windows XP SP2 and Windows Server 2003 SP1 include a loopback check security feature that is designed to help prevent reflection attacks on your computer. Therefore, authentication fails if the FQDN or the custom host header that you use does not match the local computer name.

In the case of a clustered sql instance, sql services are starting with the virtual instance name, so this is a violation of the loopback check security feature. To avoid this, exclude your hostnames from the loopback check.

Hope this helps,

Tom