SQL Server Performance

Using more than 2GB with W2K Std and SQL2K EE???

Discussion in 'Performance Tuning for Hardware Configurations' started by Todd Robinson, Jul 27, 2004.

  1. Todd Robinson New Member

    I've seen conflicting information on this and would like to know what the general concensus is on getting SQL to use more than 2GB of RAM on Windows 2000 Standard.

    I have a DL580 with 4GB of Ram and would like to have SQL use 3GB of it as opposed to the default 2GB. What switches can I set or enable to do it, or do I just have to go to Windows 2000 Advanced or Windows 2003 Standard in order to do it[?]
    Todd Robinson, Jul 27, 2004
    #2

  2. Luis Martin Moderator

    Windows 2000 Std can manage up to 4Gby.


    AWE Memory and System Resources
    Instances of SQL Server 2000 do not dynamically manage the size of the address space when you enable AWE memory. Therefore, when you enable AWE memory and start an instance of SQL Server 2000, one of the following occurs:

    If sp_configure max server memory has been set and there are at least 3 gigabytes (GB) of free memory available on the computer, the instance acquires the amount of memory specified in max server memory. If the amount of memory available on the computer is less than max server memory (but more than 3 GB), then the instance acquires almost all of the available memory and may leave only up to 128 megabytes (MB) of memory free.


    If max server memory has not been set and there is at least 3 GB of free memory available on the computer, then the instance acquires almost all of the available memory and may leave only up to 128 MB of memory free.


    If there is less than 3 GB of free memory available on the computer, memory is dynamically allocated and, regardless of the parameter setting for awe enabled, SQL Server will run in non-AWE mode.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

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

    Luis Martin, Jul 27, 2004
    #2

  3. ykchakri New Member

    Also, you need to have SQL Server Enterprise Edition for it to be able to use more than 2GB.
    Look under the topic 'Maximum Capacity Specifications' in BOL.
    ykchakri, Jul 27, 2004
    #2

  4. derrickleggett New Member

    You also need to have the /3GB switch in the boot.ini file or 2gb is reserved for the OS. the /3GB switch switches it to use 1GB for the OS and 3GB for applications. If you do this, you should disable any services you don't need. You should also make sure you don't have any system critical applications running on the server since it's a dedicated SQL Server (it sounds like).

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Jul 27, 2004
    #2

  5. satya Moderator

    satya, Jul 27, 2004
    #2

  6. Todd Robinson New Member

    Just so I'm clear. I have my Win2K (Not advanced) with SQL 2K EE

    I want to use more than the default 2GB of memory.

    I just have to set max memory to 3072, right?

    No /3gb, No AWE

    Todd Robinson, Aug 4, 2004
    #2

  7. derrickleggett New Member

    You need to have the /3gb switch in the boot.ini startup file. You don't need awe or the /pae switch. You don't need to set the max memory for SQL Server at all. Just let it dynamically manage it. the other 2gb isn't available for applications at all. It's reserved for the OS.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Aug 4, 2004
    #2

  8. biged123456 New Member

    From what I have seen, you cannot use the /3GB switch with Windows 2000 standard.
    http://support.microsoft.com/default.aspx?scid=kb;en-us;274750#3
    ---
    You cannot allocate more than 4 GB of physical memory to an application on Windows 2000 Server because Physical Address Extension (PAE) is not available on Microsoft Windows 2000 Server. Also, you cannot use the 3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000 Advanced Server or Microsoft Windows Datacenter versions.
    ---
    biged123456, Aug 13, 2004
    #2

  9. derrickleggett New Member

    My bad on that one. The only thing I have inhouse is Windows 2000 Advanced Server and 2003.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, Aug 13, 2004
    #2

  10. dlepak New Member

    I just want to clarify a situation that I have where I am at.
    I have a Windows 2000 server (not advanced) with SQL 2000 standard edition. This server has 4GB of ram in it. If I go to SQL server properties in the enterprise manager and go to the memory tab it shows the following. SQL is set to dynamically configure SQL server memory. The minimum is set to 0MB and the maximum is set to 3920MB. Even though the enterprise manager shows that almost 4GB of memory is available it will never use above 2GB. SQL will not dynamically grow the memory past 2GB. Is this correct? If I want the system to use all 4GB of memory I will have to go with SQL 2000 enterprise edition.
    dlepak, Sep 2, 2004
    #2

  11. Twan New Member

    You should be able to get SQL to use 3GB by setting the switch in the boot.ini. That shouldn't be dependent on the application, as it is an inherent Windows design limitation that you're changing by using that switch

    Cheers
    Twan
    Twan, Sep 2, 2004
    #2

  12. derrickleggett New Member

    You are right dlepak. Twan, he can only use that switch on datacenter or advanced. If you want to use all 4gb of memory, you will need to have Windows 2003, Windows 2000 Advanced, or Windows 2000 Data Center (which of course you won't get <img src='/community/emoticons/emotion-1.gif' alt=':)' /> ). You will also need SQL 2000 Enterprise Edition.<br /><br /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
    derrickleggett, Sep 2, 2004
    #2

  13. Twan New Member

    ah that's right it wasn't until windows 2003 that the 3gb switch was allowed for the standard edition...

    Twan
    Twan, Sep 2, 2004
    #2

  14. dlepak New Member

    Thanks for the replies!
    dlepak, Sep 3, 2004
    #2

Share This Page