SQL Server Performance

Allocating Memory for Multiple Instances and Cluster

Discussion in 'SQL Server 2005 Clustering' started by sql_jr, Oct 5, 2007.

  1. sql_jr New Member

    Currently have a x64-bit 2005 Active-Active Cluster with 16GB of RAM available on each node. The question is, do I need to set a min and/or max memory setting for each instance (64-bit), and how much should I allocate for optimum performance, ensuring in the case of failover (2 instances on one node) that each has sufficient memory. Or does the 64-bit architecture handle all this automatically? THX for clarification!
  2. satya Moderator

    In general better to leave the memory settings to DYNAMIC rather than setting up max and min.
  3. sql_jr New Member

    While I agree, but are you specifically basing your recommendation on the fact that its 64-bit?
    There's an MSDN article that says to set max and min, but DOESN'T specify 64-bit, so assume its referring to 32-bit. Please confirm! THX!
  4. sql_jr New Member

    I did not get a satisfactory answer. Anyone? THX!
  5. thomas New Member

    Yes, for 64-bit, I strongly recommend setting a minimum. And if you have Enterprise Edition make sure you enable the 'Lock Pages In Memory' permission.

    64-bit is not very good at taking as much RAM as it should/needs to, in my experience.
  6. sql_jr New Member

    Thanks, Thomas! So you are saying set a minimum threshold, but leave the max to dynamic setting? Interesting, that 64-bit can't handle this part well - I thought 64-bit was better equipped for this? What about the WRM? THX!
  7. thomas New Member


    Yes, leave the max as dynamic, Monitor the mem usage of each instance closely.
  8. sql_jr New Member

    Sorry for delay in reply! WRM - windows resource manager.
    Based on my research, I am coming to the conclusion that in such a case, as you have said, setting the MIN RAM for each instance is GOOD, so in the case that another instance becomes active on the same node, the original instance will maintain the MIN RAM set, and only release memory to the second instance except the minimum - This makes sense now. What have you seen where x64-bit is not good at taking min ram?
    Any other expert opinions, or confirmation of the above strategy?

Share This Page