SQL Server Performance

4 GB RAM

Discussion in 'Performance Tuning for DBAs' started by johnmcmains, Oct 31, 2002.

  1. johnmcmains New Member

    My SQL Server 2000 Enterprise (Win2000Server) box now has 4GB RAM. I assume that by adding the /3GB switch to the boot.ini file, the os will use 1GB and SQL Server will use 3GB. Do I need to do anything else? Will SQL Server 2000 dynamically use the addition memory (right now it is only using 2 GB ram).

    Thanks in advance,
    john
  2. bradmcgehee New Member

    Based on what you described, adding the /3GB switch is all you need to do, SQL Server will do the rest for you automatically, that is assuming that you still have your SQL Server memory setting set to dynamic and have not manually set a fixed memory setting.

    See this article for more details:

    http://www.sql-server-performance.com/awe_memory.asp


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. HarryArchibald New Member

    I am unsure as to whether to use the /3GB switch. Our server has 4GB of RAM and Windows 2000 Server nut it has not been used because of the info in Q291988. This states that the /3GB and /PAE switch are not supported on Windows2000 Server. Does anyone have experience of using this switch in production?

    TIA
    Harry

  4. satya Moderator

    Harry, we use this switch in one of our production server which is Win 2K Adv.server and this is enabled for this O/S. So if you need to use then upgrade the O/S to Adv. Server(if possible).


    Satya SKJ
  5. HarryArchibald New Member

    I am hoping to get the budget for AdvancedServer next year. In the meantime, we have 4GB of RAM on the server and SQLServer using only 1.7GB of RAM[<img src='/community/emoticons/emotion-1.gif' alt=':)' />] <br />
  6. bradmcgehee New Member

    I also want to confirm that you need Windows 2000 Advanced Server and SQL Server 2000 in order to use the /3B switch.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  7. satya Moderator

    Harry, until then leave the memory settings as it is.
    And also test setting max and min memory settings, it may help.

    Satya SKJ
  8. Jonathan New Member

    Originally posted by bradmcgehee

    I also want to confirm that you need Windows 2000 Advanced Server and SQL Server 2000 in order to use the /3B switch.

    To use more than 2GB with SQL Server and the latest software versions, you need Windows 2000 Advanced (or Datacenter) Server, and you must have SQL Server Enterprise or Developer Edition as well.

    --Jonathan
  9. mniazi New Member

    I talked to MS engineer and he gave me following replay

    This is definitely a very confusing topic.
    4GB of physical memory and 4GB of addressable virtual memory is not the same thing. And it is this that really generates the confusion.
    Please let me know if this helps to clear things up for you.

    PAE is a physical extension to the hardware that the operating system can take advantage of to address more memory. AWE is an API set that allows an application to reserve memory outside of the scope of the operating system memory manager. AWE can take advantage of the memory that is visible through the PAE extensions and reserve GBs of memory, but AWE does not depend on PAE in any way to work, nor vice versa. However, without PAE enabled, AWE is only able to reserve less than 4 GB of memory.

    Per Large Memory Support Is Available in Windows 2000 (Q283037) the article states
    "Without PAE, AWE is unable to reserve memory in excess of 4 GB." but AWE can still be used.

    SQL Server 2000 Enterprise Edition
    ----------------------------------------------------------------------------------
    - No switches (Standard Server, Advanced Server, Data Center) on a 4 GB system.
    By default the application (SQL Server) can request memory calls up to 2 GB.
    Plus any additional memory that the kernel can provide through AWE calls typically up to approximately 3.5 GB.

    - With the /3GB switch (Advanced Server and Data Center only) on a 4 GB system
    By default the application (SQL Server) can request memory calls up to a 3 GB limit of virtual address space.
    Plus any additional memory that the kernel can provide through AWE calls typically up to approximately 3.5 GB.

    I think the statement made in the posting below "AWE is for memory configurations over 4gb" is misleading and is based on the following statement in the article which should include "when using PAE with AWE"

    AWE is a set of application programming interfaces (APIs) to the memory manager functions that enables programs to address more memory than the 4 GB that is available through standard 32-bit addressing. Again 4GB of physical memory and 4GB of addressable virtual memory is not the same thing. And it is this that really generates the confusion.
    Notice in the same article we see the following statement Without PAE, AWE is unable to reserve memory in excess of 4 GB. It does not state that we cannot reserve memory less that 4GB.

    I had worked with a customer on Windows 2000 Server that could only utilize 1.7 GB of memory which could be seen in the performance counter TargetServerMemory.
    (Windows 2000 Server not Advanced Server does not support the /3GB with user mode applications) As a result we enabled AWE and set max server memory to 3.5 GB and TargetServerMemory reflected that.

    Note: SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth.

    If this helps, the information discussed in HOW TO: Configure Memory for More Than 2 GB in SQL Server - ID: Q274750 had been reviewed by not only SQL Server development but Windows 2000 development as well.
  10. HarryArchibald New Member

    Many thanks. This answers my question exactly.

    Cheers,
    Harry

Share This Page