SQL Server Performance

Memory question

Discussion in 'Performance Tuning for Hardware Configurations' started by rasser, Aug 6, 2003.

  1. rasser New Member


    I can't work out the exact requirements/config for utilizing more than 2GB RAM.

    I have just installed a new server for a client. It's a Proliant DL380 with 2 x 2.4 MhZ Xeon CPU and 3GB RAM + RAID ctrl with 2 x 36GB for system (RAID 10) 2 x 72GB for Data and 2 x 72GB for log. The database is 6.7GB in size

    I have installed Windows 2000 standard server with the 3GB switch in boot.ini, and installed SQL Server Enterprise Ed. with no additional configuration other than standard installation.

    The server however refuses to use more than 1.725GB - it goes rather quickly to that amount, but then refuses to use any more than that.

    I have been trying to look up on Knowledge base for more information on this, but don't quite understand what i found - can someone "cut it out in cardboard" for me please.

    Q1. Does Windows 2000 Server Std. only support 2GB (I thought it was 4GB if using the 3GB switch)

    Q2. What would the exact steps be to enable the full use af the 3GB RAM - based on the above info regarding Hardware.

    Please help


    Bjarne Rasmussen
  2. Luis Martin Moderator

  3. joechang New Member

    Q1. OS needs to be W2K Advanced Server or W2K3 EE to use the 3GB switch.
    while the OS can use 4GB with standard edition, each process, ie, sqlservr.exe has only 2GB for user mode code, and the other 2GB is protected OS space.
    AS & EE lets you change the split from 2/2 to 3/1
    not only that, but you can also use more than 3G with PAE

    next, for your app to use more than 2GB, you need SQL EE, which you already have,

    Q2. so you need to upgrade W2K Std Ed to Adv Srv. Ed.
  4. gaurav_bindlish New Member

  5. rasser New Member

    OK thank's I have seen this - and that's what I don't quite understand - sorry my english <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />I se this part:<br />Windows 2000 Server<br /><br />SQL Server 2000<br />Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition can use up to 2 GB of physical memory. With the use of the AWE enable option, SQL Server can use up to 4 GB of physical memory. <br /><br />NOTE: 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. For additional information, click the article number below to view the article in the Microsoft Knowledge Base: <br />291988 A Description of the 4 GB RAM Tuning Feature and the Physical Address Extension Switch <br />--------------------------------------------------------<br /><br />So does that in other words mean that I have to upgrade to at least "Advanced server"?????<br /><br />
  6. rasser New Member

    Hi again

    joechang - thank's for your reply - I understand although my client wont be happy about the news.

    Doing this will it then (sqlserver.exe) use all of the 3GB - leaving say 128mb for the OS - do you know??

    Thanks to you all for the quick replies!!!!
  7. gaurav_bindlish New Member

    I think having AWE enabled requires 1 GB of memory for the OS. So I guess you'll have a maximum available memory of 2 GB even if you upgrade to Win2K Advanced Server.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  8. rasser New Member

    Ok so reading this article again....

    ---- Cut ----------------------------
    SQL Server 2000
    Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options:

    Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 4 GB of memory.

    NOTE: To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly.

    When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.

    back to the top
    The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option: sp_configure 'show advanced options', 1
    sp_configure 'awe enabled', 1
    sp_configure 'max server memory', 6144


    -or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory.
    ---- Cut ----------------------------

    Right on the last line -or-Use etc....

    If I understand that right.. If I don't to use the AWE switch, SQL Server will use all it can up to 3GB and so therefore not swallow up 1 GB for the OS????
  9. gaurav_bindlish New Member

    See the line
    quote:When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE
    This is what made me statement like that.

    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  10. rasser New Member

    OK thank's <br /><br />I found this in another post....<br /<a target="_blank" href=http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1011&SearchTerms=/3GB>http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1011&SearchTerms=/3GB</a>,<br /><br />-----Cut--------------<br />Justin, yes, set /3GB and not /PAE or /AWE. You only need them for MORE than 4GB. If you set /3GB you should see SQL Server take up to about 2.8Gb.. I never see it take more than that on our system, but it isn't short of RAM so I'm not concerned about pushing it even further.<br /><br /><br />Tom Pullen<br />DBA, Oxfam GB<br />-----Cut--------------<br /><br />So I'll just try and Upgrade to "Advanced Server" and watch and see<br /><br />Thanks all for your help - it's been great <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br />

Share This Page