SQL Server Performance

Where's that memory?

Discussion in 'Performance Tuning for DBAs' started by justin.hannan, Jun 26, 2003.

  1. justin.hannan New Member

    Wonder if anyone can confirm the following scenario solution;

    Windows 2000 Advanced Server with SQL 2000 Enterprise
    Server has 4GB RAM but the O/S & SQL only use 2GB of it

    What I've done is...

    1. Changed the BOOT.INI to have the /PAE parameter
    2. Enabled AWE in SQL

    I did see a /3GB option for the BOOT.INI but this didn't quite seem as relevant as the /PAE option.

    On rebooting (apart from our SAN HBA popping [:0]) the O/S shows 4GB available as total memory, available is ~200MB but when I look at the sqlservr.exe process it's only using ~205MB (I thought it would start caching and using up the 4GB pretty quickly as the DB is heavily used by approx. 1000 users).

    My question is, has anyone done this themselves? where has the other 3.5GB gone (has sql reserved it and the taskmanager can't see it?

    Should I have set /3GB in the BOOT.INI instead of /PAE and left AWE off in SQL?


  2. satya Moderator

    Collect PERFMON counters for memory and other process to assess the situation.

    Satya SKJ
  3. thomas New Member

    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.

    Tom Pullen
    DBA, Oxfam GB
  4. gaurav_bindlish New Member

    Yes thomas is right. U don't need the /PAE switch until u go beyong 4 GB. U just have to use the /3GB switch. 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. NOTE that task manager shows wrong memory after enabling AWE options. I am not sure if this is the case with /3GB switch as well. If it is, use system monitor to verify the behaviour.

    Seehttp://www.sql-server-performance.com/awe_memory.asp for more details.

  5. bradmcgehee New Member

    Using the /3GB switch will not negatively affect how Task Manager displays memory.

    Brad M. McGehee, MVP
  6. BigJimSlade New Member

  7. precision New Member

    Hey guys, I'm not a DBA but I recently was hired as the solo I.T. person for a clinic that is running SQL Server and have noticed that sqlserver.exe continously eats up ram until it had used up most of our 4GB and the system needs to be rebooted. This is are only server and everything goes down when I reboot (DNS, DHCP, A.D., etc.). Will this /3GB switch limit sqlserver to only using three gigs and how do I use it?
  8. derrickleggett New Member

    You need to set the maximum on the server to some range that will not allow it to bring down the server (leave .5gig or so). You can do this with sp_configure. Books Online explains it pretty well.

    Also, if you have Advanced Server and Enterprise Edition, you should shell out some money for more RAM. It's cheap and the biggest performance increase you can get.


    When life gives you a lemon, fire the DBA.

Share This Page