SQL Server Performance

'AWE' Question

Discussion in 'Performance Tuning for DBAs' started by egsj, Jul 15, 2006.

  1. egsj New Member

    Hi, another AWE question. Yes, I have checked the following threads but still have further questions.


    I have a dedicated 8 GB RAM w2k adv server (OS) with sql2k sp4+hot fix kb899761, which is to take care of AWE issue after applied sp4. 'lock pages in memory' rights is granted for service account. /3gb /pae paramenters are set in boot.ini, and 'awe' is enabled insider sql server. 'min server memory' and 'max server memory' are set to 6.5 GB. 'address windowing extension enabled' is recorded in sql server errorlog.

    However, on task manager, the sqlserver.exe process shows memory usage is only at 102 MB, with 14 million page fault and incrementing. There is some articles on the net indicating that task manager usually won't report accurate memory usage for sql server once AWE is enabled. Is this true? Perfmon sqlserver:memory manager otal server memory does report 6.5 GB.

    Also, do we need to change 'set working set size' from 0 to 1? Currently it's 0 on this server.

    Thanks a lot in advance for any advice/response.

  2. joechang New Member

    in w2k, task manager process memory is probably a 32-bit value, signed or unsigned
    so any number bigger than 2-4GB is reported incorrectly
    i think this may have been fixed in w2k3, but i am not really sure since i moved most of my systems to 64-bit
  3. merrillaldrich New Member

    egsj - I would be more inclined to trust Performance Monitor for the SQL server memory usage counters to get a clearer picture of what is happening. I don't think AWE memory will be reported correctly in Task Manager, but you can see it with the Perfmon counters.
  4. egsj New Member

    Thanks for the reply. After checking a few perfmon counters, I'm pretty sure that AWE is effective. What interests me is that why there is so much page faults for sqlserver process given a large RAM allocated. It's likely that those are soft page faults, but should this still be a concern? Is there a need to change 'set working set size' and reserve the 6.5 RAM upon instance startup?
  5. joechang New Member

    which counter are you looking?
    Memory -> pages/sec
    or page faults/sec,

    just pay attenttion to pages/sec which should zero most of the time
    ignore page faults, as that includes soft faults

Share This Page