SQL Server Performance

Yey another Memory Q

Discussion in 'Performance Tuning for Hardware Configurations' started by stefanoale, Aug 14, 2003.

  1. stefanoale New Member

    I have Win2K server with SQL2K.
    4gb of actual ram.
    I enabled the AWE option.
    Now on windows task manager I can see that the total Committed memory is 3GB+ but the sqlserver process is only using 78MB.

    Is there a way I can check that the SQL process is using the whole 3GB I specified in the SQL memeory settings?

    Thanks in advance.
  2. bradmcgehee New Member

    Generally speaking, you don't need to enable AWE memory if you only have 4GB of RAM, just using the /3GB is adequate. Also keep in mind that you have to have SQL Server Enterprise to take advantage of more than 2GB of RAM.

    To see how much RAM SQL Server is using, watch the SQL Server:Memory Manager Total Server memry (KB) Performance Monitor counter.

    See this url for more information:http://www.sql-server-performance.com/awe_memory.asp

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  3. fullbrij New Member

    Unless you're running SQL Server Enterprise on Windows 2000 Advanced Server, don't.

    1. Windows 2000 Standard can't make use of either switch.
    2. Only use the AWE extentions if have more than 4GB of RAM
    3. You could gain an extra 1GB for applications from using the /3GB switch with 4GB installed, if you're W2K Advanced/ SQL Enterprise.
    4. If you use the /3GB switch, you severely limit the number of available System Page Table Entries. This can have a negative impact under heavy IO loads [Read SQL] or even cause a stop error [stop 0x0000003F] if you exhaust them. You'll want to monitor this counter closely, and if it falls below 10,000 then consider manually tuning system pages.
    5. If you use the /3GB switch on W2k Standard, you lose 1GB of kernal RAM and gain absolutely nothing. Don't do it.
    6. Lack of PTEs can cause lost delayed writes, ie. event ID 50, with a status code 0f C000009A. C000009a means STATUS_INSUFFICIENT_RESOURCES, and generally refers to running out of PTEs.

    John Fullbright
    MOSMWNMTK

Share This Page