SQL Server Performance

/3gb and /PAE switch?

Discussion in 'Performance Tuning for DBAs' started by satishjk, Mar 22, 2005.

  1. satishjk New Member

    I have a question regarding the /3GB and /PAE switch. We have SQL 2000 enterprise edition running on Windows 2003 enterprise server. We have 6GB of physical memory. I have assigned 4GB to SQL server thru enterprise manager. /PAE is turned on and when I check perfmon it shows SQL memory used in 3GB's. But I am not sure whether to trust it since in the task manager sqlservr.exe shows only 100mb's being used by SQL server. But in the performance TAB of the task manager it shows 4GB's used in total. Can someone please shed some light on this? Is it required that /3GB switch to be on? Is there someother way to find how much memory is being exactly used by SQL server and how effectively it is using AWE?

    Thanks for all your help.

  2. Luis Martin Moderator

    To find out use Performance Monitor (total SQL server memory, target SQL server memory), no Task Manager.

    Luis Martin

    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. satya Moderator

    With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. derrickleggett New Member

    Here are some details for you to think about that kind of uses what Luis and Satya said.

    1. If you have the /3GB and /PAE switches both on, then SQL Server (or any application) can access up to 5GB of physical RAM since you have 6GB on the server. The difference with the /3GB switch is that you will not have use the memory extensions for the first 3GB instead of 2GB without it.

    2. You need to make sure that SQL Server has "awe enabled" turned on. It sounds like you do from what you have told us. If you're not sure, you can run:

    USE master
    EXEC sp_configure 'show advanced option', '1'
    EXEC sp_configure

    3. As Luis said, look at the target and total server memory to see how much RAM SQL Server is actually using. Total is what it's using. Target is what it would like to have.

    4. When SQL Server claims the RAM, it doesn't necessarily ever release it. That's why you need to set the maximum size for RAM in your current setup. That's also why you can sometimes see only 100mb for example being used. It has reserved 3GB because that's what it claimed at the maximum usage level and didn't release. It is currently using 100mb.

    Now, for your last question. This is actually the most interesting of all the questions you asked. You can look at articles here, on the free webcast area at www.microsoft.com, and www.sqlteam.com for information on memory internals and usage with SQL Server. If you look at the buffer cache hit ratio and the target/total server memory, you get a good idea of the overall performance of your memory with SQL Server. You can then drill into it by looking at the cache hit ratios for each individual area, looking at how fast items flush through the cache, etc. It's an entire subject area to research. Let me know if you need more direction on this one.


    When life gives you a lemon, fire the DBA.
  5. BalajiG New Member

    Hi Satish,
    Normally if you use /pae it will use the memory which is more than 4 GB. What you could in your case just check the following checklist and confirm if anything missed out
    Steps for using more than 4 GB for SQL Server
    1. Check the OS Memory (if it is more than 4 GB and less than 16 GB)
    1. Add /3GB and PAE in Boot.ini file (PAE is required when we need to use more than 4 Gig of Memory)
    1. Enable AWE Memory option using SP_CONFIGURE
    1. Set the Max Memory as per the requirement
    1. Set the lock Pages in memory for the SQL Startup account (if this is not given SQL Server will not utilize the available memory, which is more than 4 gig)
    1. Restart the Server
    1. Check the available server memory (it should match with the memory allocated to the
    SQL Server) from the sysperfinfo table using the below Query:SELECT cntr_value/1024 as 'MBs used'from master.dbo.sysperfinfowhere object_name = 'SQLServer:Memory Manager' andcounter_name = 'Total Server Memory (KB)'
    Kindly let me know if anything else
    All the best
    Balaji G
  6. rgs New Member

    Hi Balaji -
    Your summary is very helpful and in agreement with all of my research on this issue, but I am experiencing a problem I can't figure out.
    We have 12GB on our HP DL380 G4 server. I have set the Max Memory to 10240 and followed all of your steps. But when I check on the memory usage it is only 6,068 KB, or half of my available memory. I have experimented with various other settings but to no avail. Any ideas why it is only half of my total memory and how I can make use of all of the memory?
  7. satya Moderator

  8. rgs New Member

    Sorry, should have included that! It is SQL Server 2000 Enterprise SP4 running on Windows 2003 Server Enterprise 32 bit, clustered. Only a single instance of SQL is installed.
    Sounds like http://support.microsoft.com/kb/899761/ might fix the problem. I'll give it a try.
  9. satya Moderator

    Then follow the links on the above blog for more monitoring.
  10. rgs New Member

  11. rhunt New Member

    I hate to keep a resolved thread going but I'm a bit unclear on both switches. I've gone through all the steps for enabling AWE and that is all working. However, I'm unclear as to whether I need both switches, one, or neither.
    I am running SQL Server 2005 Standard on Windows Server 2003 Enterprise with 6GB RAM. I'm getting the impression that /3GB alters virtual address space and PAE enables greater than 4GB of physical memory...? Is that correct?
    Currently my OS shows that all 6GB is available. When SQL starts up it sees 4GB.
    Can someone tell me if I need none, one, or both switches?
    Thanks in advance. RH
  12. satya Moderator

  13. matrixchyah2005 New Member

    This was one amoung the best thread which helped me to solve the issue as i followed all the steps including the microsoft patch,now my WIN2003 ENT server with sql2000 ENT is started consuming alomost 7GB RAM which i dedicately allocated.
    Now my current problem is as i reserverd the memory to SQL server how can i know currently how much memory SQL its consuming(To know still paging is happening or not and whether it needs more ram), when i checked Performance counters Target server and Total server its showing both around 7 GB(where i need is to know how much RAM is currently consumed).
  14. satya Moderator

Share This Page