SQL Server Performance

AWE and Virtual Memory Paging Files

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by eleung, Apr 9, 2008.

  1. eleung New Member

    Hi Everyone,
    The following article I read puzzles me regarding the Paging File, http://technet2.microsoft.com/windowsserver/en/library/00284c8d-7a42-40f2-8a01-8de61dccd8c91033.mspx?mfr=true.
    "... Under such circumstances, the effectiveness of this large of page filecould be minimal, based on the usage of the system, such as whenhosting database management applications..."
    So my questions are the following:
    1. Does SQL Server 2000 Enterprise or SQL Server 2005 32-bit utilize the Page Files if AWE is enabled withPhysical Memory fixed at 6 GB out of total 8 GB in the Windows 2003 SP132-bit?
    2. Are Paging files needed for 64-bit Windows 2003?

    Thanks,
    Eric

  2. MohammedU New Member

    In 32 bit SQL can use only 2 GB unless AWE enabled and it may use page file depends on the usage of the server/memory...
    Yes, page file is required for 64 bit too...but AWE is not required...
  3. eleung New Member

    Thanks MohammedU. Our SQL 2K EE server is configured with AWE and 6 GB RAM and only 2-4 GB Page File. I did a SysMon on the % Page File yesterday through this morning and the peak % usage on the PageFile is merely 10.821%. Now, we have the backup jobs and db maintenance jobs running last night. That means our server would be very busy and may utilize a lot of the Paging File. 10% peak usage is really surprising.
    Does any one out there knows for a fact that SQL doesn't use the Paging File if AWE is turned on? Or is the 6 GB is sufficient for SQL not to touch the Page File?
  4. qanbari New Member

    I have 2 sql 2000 ent. A/P nodes installed on w2k3 ent. 32 bit. each with 16 GB RAM. SQL installed on c: and databases on e: and log files on l: on SAN.
    What should be the optimum page file size? Should it be created on all drives to improve performance? I am facing slowness problems during peak times.
    Thanks in advance.
    Mohammad Qanbari
  5. satya Moderator

  6. rohit2900 Member

    I've below configuration on one of my production box.
    SQL Server 2000 Enterprise, 32 Gigs RAM, 2 Xeon processors, on C drive 1024 MB page file and 9000 MB each on two different partitions and both are on different RAID arrays. Apart from thsi our Java application is also running on the same box.
    Now my question is I've read that we cannot total page file size beyond 4096 MB on a 32 bit machine. then how its like this. and secondly, yesterday we got alert "Virtual memory uses 99%". one of our on call DBA restarted the server and it was fine. But I just want to know the root cause of the issue and wanna know if this page file configuration is fine or not.
  7. rohit2900 Member

    Any clue guys... its bit urgent....
  8. satya Moderator

    Ok I have been absent on forums for a while, anyway can you check the amount of disk space available on C: drive or on the disk where this pagefile is located.
    I guess it could be a 2 fold issues with Mmeory and disk space.
  9. moh_hassan20 New Member

    [quote user="rohit2900"]I've read that we cannot total page file size beyond 4096 MB on a 32 bit machine. then how its like this.[/quote]
    You can overcome the 4GB file size limit and create more size
    How to configure >4GB read:
    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q237740

Share This Page