SQL Server Performance

Active/Active with 16GB RAM

Discussion in 'SQL Server Clustering' started by stevem123, Sep 22, 2006.

  1. stevem123 New Member

    I've seen the articles about AWE and setting MAX SERVER MEMORY and not having total MAX SERVER MEMORY for both active instances totaling more than the total memory. Just want to confirm my settings and ask some other questions...

    I have 16GB of RAM, Windows 2003 Enterprise and SQL 2000 Enterprise. This server is dedicated to being an ACTIVE/ACTIVE SQL Server CLUSTER.

    On the OS side...
    /3GB and /PAE are enabled in boot.ini (some seem to question /3GB or not)

    On the SQL side...
    AWE enabled in SQL 2000 EE (set to 1) for each active instance
    MAX SERVER MEMORY is set to 7168 for each active instance (7GB)
    MIN SERVER MEMORY is set to 0 (default)
    [in a failover situation, I assume this means 16-7-7=2GB left for the OS and other apps]

    Questions...

    1. Should a MIN SERVER MEMORY be set to 7168 or something more than 0? Any performance gains there?

    2. With MIN SERVER MEMORY at 0, SQL will dynamically adjust to what it needs between 0 and 7168 and use any remainder for other apps if they need it?

    3. With 16GB RAM and wanting to be able to run ok in a failover is allocating 7GB to each of the two instances a good amount? Is 2GB enough for the OS, cluster needs and other apps?

    4. If I set each's MAX SERVER MEMORY to 10GB or 12GB and a MIN SERVER MEMORY at 0, if a failover occurs, the instance on the non-failed server would have 10 or 12GB and then the failing over instance would try to take on as much as the remaining 6 or 4GB. Does that give it enough to not struggle so much to come online so I dont have to "waste" as much memory by setting a MAX < 50% server memory when 99% of the time it is not failed over?

    5. Any other recommendations?

    THANKS FOR YOUR HELP...
  2. Argyle New Member

    As far as I know once you set awe=1 sql server will grab the amount specificed in max_server_memory. It's not dynamic. So 7+7 sound ok.

  3. rabehma New Member

    I agree. As far as i have seeb my SQL Servers, when AWE is set to 1 the server takes all memory. You will not see all allocated memory under "mem usage" in the Task Manager, but it is realy allocated by SQL.

    2 GB is enough for OS, assuming that both server are running only SQL.

    Next time you will be changing servers and moving to SQL2005, consider using 64 bit machines (EM64T). The price is the same like 32bit. The 64bit is better for SQL because no redirection is made and, therfore, performance is better that AWE.


Share This Page