none
Memory setting for multiple instances

    Question

  • Hi,

    I have the new production server with two instances on 32 bit system with SQL 2005. For the best performance do I need to set up the max memory for each instance?

    I have total of 14 GB ram on that server so if I need to set up do I need to split the memory 6 GB to each instance or how should I do that. Both instances  have same kind of functonality. Please advise me as soon as possible, I need to up that by tomorrow. Thanks in advance.


    kp
    Friday, May 01, 2009 2:24 AM

Answers

  • The short answer is YES, you need to set max server memory on both of them, but how you actually go about doing it really depends on the workloads that each will be doing, and what version of SQL Server you are using.  You could try splitting them 6GB and 6GB, but that may not actually fit your workload needs.  If you have one instance that is heavier for workload it might be better to do a 8GB/4GB split, keeping in mind that you might need more than 2GB available to the OS and for other features that may be running like Full Text Indexing, SSIS, SQL Server Agent, and any other processes on the server itself.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 01, 2009 3:00 AM
  • This is a "it depends" kind of question.  I'll supply a couple of answers, but you'll have to make a choice.

    If you DON'T set a Max Server Memory value for both instances, you run the risk of them fighting over memory.  This is probably the least desirable option, and can lead to instability of performance (not server instability, but you'll never know when once instance has memory or not!)

    Assuming the databases on each side have the same amount of activity, and they are both have similar sized databases, I would recommend setting Max Server Memory to 6 GB on each as you've described.  If one instance will run a single 3 GB database, then there's no need to give that instance 6 GB - let the other instance have 9 GB.

    What it will come down to is testing.  You can turn on AWE and set Max Server Memory for both instances to 6 GB to begin with, and then monitor such counters as Total Server Memory, Page Life Expectancy or Buffer Cache Hit Ratio.  Based on the results of these values, you can reconfigure the Max Server Memory, and it will dynamically change - no outage required.  For example, you notice that one instance is starved for memory (low Page Life Expectancy) while the other is having no problems.
    Friday, May 01, 2009 3:00 AM

All replies

  • The short answer is YES, you need to set max server memory on both of them, but how you actually go about doing it really depends on the workloads that each will be doing, and what version of SQL Server you are using.  You could try splitting them 6GB and 6GB, but that may not actually fit your workload needs.  If you have one instance that is heavier for workload it might be better to do a 8GB/4GB split, keeping in mind that you might need more than 2GB available to the OS and for other features that may be running like Full Text Indexing, SSIS, SQL Server Agent, and any other processes on the server itself.

    Jonathan Kehayias
    http://sqlblog.com/blogs/jonathan_kehayias/
    http://www.sqlclr.net/
    Please click the Mark as Answer button if a post solves your problem!
    Friday, May 01, 2009 3:00 AM
  • This is a "it depends" kind of question.  I'll supply a couple of answers, but you'll have to make a choice.

    If you DON'T set a Max Server Memory value for both instances, you run the risk of them fighting over memory.  This is probably the least desirable option, and can lead to instability of performance (not server instability, but you'll never know when once instance has memory or not!)

    Assuming the databases on each side have the same amount of activity, and they are both have similar sized databases, I would recommend setting Max Server Memory to 6 GB on each as you've described.  If one instance will run a single 3 GB database, then there's no need to give that instance 6 GB - let the other instance have 9 GB.

    What it will come down to is testing.  You can turn on AWE and set Max Server Memory for both instances to 6 GB to begin with, and then monitor such counters as Total Server Memory, Page Life Expectancy or Buffer Cache Hit Ratio.  Based on the results of these values, you can reconfigure the Max Server Memory, and it will dynamically change - no outage required.  For example, you notice that one instance is starved for memory (low Page Life Expectancy) while the other is having no problems.
    Friday, May 01, 2009 3:00 AM
  • For a server with 14 GB RAM, you really out to be considering a move to a 64-bit OS, SQL edition, and hardware.

    That said, all you ever wanted to know about memory in SQL Server can be found on Slava Oks' WebLog:


    It's pointless trying to summarise the issues in a forum post, when the subject is so complex, and such an excellent resource already exists.

    Do give it a read.

    Paul
    Friday, May 01, 2009 11:37 AM
  • To enable more than 2GB memory to 32bit server you have to enable AWE then you can cofigure the max server memory

    Steps to enable AWE windows server 2003:

    1. Before enabling AWE, you must configure the Lock Pages in Memory policy. For more information, see How to: Enable the Lock Pages in Memory Option (Windows).

    2. 
    sp_configure 'show advanced options', 1
    RECONFIGURE 
    GO
    
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO
    3. Restart the SQL Server to enable the AWE

    4. set max and min server memory as per suggestion by Jonathan.

    sp_configure 'min server memory', 1024
    RECONFIGURE
    GO
    
    sp_configure 'max server memory', 6144
    RECONFIGURE
    GO
    Click here for details


    Thanks,
    Nimit
    Wednesday, May 06, 2009 7:37 PM
  • If it was me, and I had a new Production DB server with 14GB of RAM that needed two instances of SQL Server, I would use the x64 version of SQL Server 2008. I also don't like to use multiple instances of SQL Server on a Production database server. I realize that might not be possible for you, for several reasons.

    At any rate, if you must use 32-bit, you will want to enable AWE, and then set MaxServerMemory for each instance. What you set it to will depend on their relative workloads and importance.  You need to leave enough memory for the OS and whatever else may be running on that box (besides the DB Engine).

    You also need to remember that each instance will be competing for all of your other resources on that machine, not just memory.
    http://glennberrysqlperformance.spaces.live.com/ Please mark as the answer if this post solved your issue.
    Wednesday, May 06, 2009 11:38 PM