SQL Server Performance

Memory and CPU Resource management in a 4-node cluster

Discussion in 'SQL Server 2005 Clustering' started by arenas87058, Nov 20, 2008.

  1. arenas87058 New Member

    Hi All,
    I'm running a 4-node cluster. When instances fail over to a different node, can I manage how much memory and cpu each instance is allocated? Are there any supporting docs?
    Thanks,
    A
  2. MohammedU New Member

    http://technet.microsoft.com/en-us/magazine/cc162320.aspx
    Allocating Cluster Memory
    Q For my active three-way cluster (a fourth node is idle), is it best to set SQL Server max server memory to one-third the total memory, or is it OK to max out the SQL Server memory and then during failover, let the failing instances fight for memory?
    It seems better to plan ahead for the failing instances since, for example, two of the four servers could go down. In such a case, I could allocate my five SQL Server instances and one Analysis Services instance to each and still have two other servers to fail over to.
    http://chrisshaw.wordpress.com/2008/09/22/managing-sql-server-cluster-memory/
    http://chrisshaw.wordpress.com/2008/09/22/managing-sql-server-cluster-memory-part-2/
    http://msdn.microsoft.com/en-us/library/ms179410.aspx
  3. satya Moderator

    What edition of SQL you are using?
  4. arenas87058 New Member

    2005.
    Someone made the comment that resources could be dynamically reallocated without restarting the instance if more than one instance failed over to a single node. Is this correct? If so, how?
    Thanks,
    A
  5. satya Moderator

    I know it is 2005, I'm asking the edition of SQL Server.
    The default memory management behavior of the Microsoft SQL Server Database Engine is to acquire as much memory as it needs without creating a memory shortage on the system. The Database Engine does this by using the Memory Notification APIs in Microsoft Windows.
    As other applications are started on a computer running an instance of SQL Server, they consume memory and the amount of free physical memory drops below the SQL Server target. The instance of SQL Server adjusts its memory consumption. If another application is stopped and more memory becomes available, the instance of SQL Server increases the size of its memory allocation. SQL Server can free and acquire several megabytes of memory each second, allowing it to quickly adjust to memory allocation changes.
  6. arenas87058 New Member

    Ok. This sounds like SQL is doing this by itself. Can we manually set the memory configuration and reconfigure with override? I don't think so considering that we will be using AWE.
    Does it matter what edition it is? If so, can you explain. It's a 4-node cluster so it must be enterprise.
    Thanks,
    A
  7. satya Moderator

    It shouldn't be doint on its own in terms of memory (mis-)management, you must ensure to have the same level of service pack on all the nodes that are involved in the cluster.
    Probably you may need to check failover to different nodes to see if there is any mismatch of memory settings, if so then you may need to refresh that node in order to take care of memory management dynamically which is better to leave it to SQL engine.

Share This Page