SQL Server Performance

/3GB /PAE question

Discussion in 'Performance Tuning for DBAs' started by bertcord, Mar 7, 2005.

  1. bertcord New Member


    I have a question about the /3gb /pae switch that I was hopping
    someone could shed some light on.

    I have read the following article
    http://www.sql-server-performance.com/awe_memory.asp

    and my question pertains to the following quote.

    "The /3GB switch is used to tell SQL Server to take advantage of 3GB
    out of the base 4GB of RAM that Windows 2000 supports natively. If you
    don't specify this option, then SQL Server will only take advantage of
    2GB of the first 4GB of RAM in the server, essentially wasting 1GB of
    RAM."

    My server has 5GB of memory. AWE is enabled and max server memory and
    min server memory is set to 4125. I am using the /PAE switch but NOT
    the /3GB

    based on this the max memory the SQL Server process could be using is 3GB

    2GB - Kernel Mode
    2GB - User Mode
    1GB - Access via AWE

    But when I run perfmon and look at Total Server Memory and this value
    is 4125. How is this possible without using the /3GB switch?
    Shouldn't this value be only 3GB?

    Bert
  2. Luis Martin Moderator

    What SO and SQL do you have?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  3. bertcord New Member

    I am running SQL 2000 Enterprise edition version 8.00.977 on Windows 2000 Advanced Server.

    Maybe I have some incorrect conclusions…so correct me if I am wrong. This is for Windows 2000 Advanced server

    1. AWE is used to access memory over 4GB
    2. To enabled AWE you must specify the /PAE switch in the boot.ini
    3. on a 32 bit OS 4GB of memory can be directly accessed
    4. a default windows install on a machine with 4GB will allocated 2GB
    to Usermode memory and 2GB to kernel mode memory
    5. you can change the kernel mode user mode allocation by using the
    3GB switch in the boot.ini, this will change it to 3GB Usermode 1GB
    Kernel mode


    Thanks
    Bert
  4. Luis Martin Moderator

    1) Yes.
    2) Using AWE Memory on Windows 2000
    Microsoft® SQL Server™ 2000 Enterprise Edition uses the Microsoft Windows® 2000 Address Windowing Extensions (AWE) API to support very large amounts of physical memory. SQL Server 2000 Enterprise Edition can access amounts of memory approaching 8 GB on Windows 2000 Advanced Server and approaching 64 GB on Windows 2000 Data Center.

    Standard 32-bit addresses can map a maximum of 4 GB of memory. The standard address spaces of 32-bit Microsoft Windows NT® 4.0 and Windows 2000 processes are therefore limited to 4-GB. By default, 2 GB is reserved for the operating system, and 2 GB is made available to the application. If you specify a /3GB switch in the Boot.ini file of Windows NT Enterprise Edition or Windows 2000 Advanced Server, the operating system reserves only 1 GB of the address space, and the application can access up to 3 GB. For more information about the /3GB switch, see Windows NT Enterprise Edition or Windows 2000 Advanced Server Help.

    AWE is a set of extensions to the memory management functions of the Microsoft Win32® API that allow applications to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE lets applications acquire physical memory as nonpaged memory, and then dynamically map views of the nonpaged memory to the 32-bit address space. Although the 32-bit address space is limited to 4 GB, the nonpaged memory can be much larger. This enables memory-intensive applications, such as large database systems, address more memory than can be supported in a 32-bit address space. For more information about AWE, see the MSDN® page at Microsoft Web site.

    Enabling AWE Memory
    You must specifically enable the use of AWE memory by an instance of SQL Server 2000 Enterprise Edition by using the sp_configure option awe enabled.

    When awe enabled is set to 0, AWE memory is not used, and the instance defaults to using dynamic memory in standard 32-bit virtual address spaces.


    When awe enabled is set to 1, AWE memory is used, and the instance can access up to 8 GB of physical memory on Windows 2000 Advanced Server and 64 GB on Windows 2000 Data Center.
    When an instance of SQL Server 2000 Enterprise Edition is run with awe enabled set to 1:

    The instance does not dynamically manage the size of the address space.


    The instance holds all memory acquired at startup until it is shut down.


    The memory pages for the instance come from the Windows nonpageable pool, meaning that none of the memory of the instance can be swapped out.
    You must carefully manage the memory used by an instance of SQL Server when awe enabled is set to 1. If the instance acquires most of the available physical memory as nonpaged memory, other applications or system processes may not be able to get the memory they need to run. Use the max server memory configuration setting to control how much memory is used by each instance of SQL Server that uses AWE memory. For more information, see Managing AWE Memory on Windows 2000.

    3)See 2)
    4) Yes
    5) Yes

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. bertcord New Member

    Ok… so where is the 4GB of memory coming from in my original post.

    If both are correct

    1. AWE is used to access memory over 4GB

    4. a default windows install on a machine with 4GB will allocated 2GB
    to Usermode memory and 2GB to kernel mode memory

    How is SQL using 4GB of memory. I only have 1GB of memory available over 4GB and I have NOT specified the /3GB switch, so this gives me only 2GB of USER mode memory…for a total of 3GB of memory.

    Thanks
    Bert
  6. satya Moderator

    Brad explainedhttp://www.sql-server-performance.com/awe_memory.asp here

    If you are using SQL Server 2000 Standard Edition under Windows NT 4.0 or Windows 2000 (any version), or are running SQL Server 2000 Enterprise Edition under Windows NT 4.0 or Windows 2000 Server, or if your server has 4GB or less of RAM, the "awe enabled" option should always be left to the default value of 0, which means that AWE memory is not being used.

    The AWE (Advanced Windowing Extensions) API allows applications (that are written to use the AWE API) to run under Windows 2000 Advanced Server or Windows 2000 Datacenter Server to access more than 4GB of RAM. SQL Server 2000 Enterprise Edition (not SQL Server 2000 Standard Edition) is AWE-enabled and can take advantage of RAM in a server over 4GB. If the operating system is Windows 2000 Advanced Server, SQL Server 2000 Enterprise Edition can us up to 8GB of RAM. If the operating system is Windows 2000 Datacenter Server, SQL Server 2000 Enterprise can use up to 64GB of RAM.




    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page