SQL Server Performance

Using pae with Win 2003 Enterprise

Discussion in 'Performance Tuning for DBAs' started by loiter99, Oct 30, 2006.

  1. loiter99 New Member

    Hello,
    I have /PAE on my boot.ini, but the server is still at 1000 page/sec on a 16 gig ram / quad processor. Does it matter if it is /PAE or /pae? All docs say /pae.

    thanks
    J
  2. Luis Martin Moderator

    What SQL version do you have?

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. joechang New Member

    sql server should not be causing the paging,
    check other applications, usage of memory, consider backing off sql a little
  4. westermanm New Member

    I thought SQL didn't cause paging? Usually a result of the OS not having enough memory. I presume you don't have the /3gb switch on as well as this will limit the OS to 1Gb for kernel resources as opposed the standard 2gb.

    How much memory have you allocated to SQL?

    Mark
  5. loiter99 New Member

    I have the /3gb switch on now, but the Target Server Memory and Total Server memory only report back 2.7 gigs. I saw a hot fix from MS that fixed a problem with AWE not seeing all available memory on a SQL server 2000 with SP 4 installed. Is anyone familiar with this?

    The server is 2003 Enterprise SP1

    Thanks
    J
  6. joechang New Member

    with out a full analysis of your app
    i would recommend using PAE, but not /3GB

    set max SQL Server memory to 14-15GB
  7. westermanm New Member

    If you have the /3gb switch on and lots of page faults, I'd suggest that the OS is short of memory, as you are telling it to only have 1gb and have the rest for applications.

    This is okay if you have only 4gb in the server, but with 16gb installed, and PAE on I think it would be better to have 2gb for the OS and 14 for SQL.

    mark
  8. loiter99 New Member

    Does the /3GB switch limit AWE?

    J
  9. westermanm New Member

    No the 3Gb switch does not limit AWE, but is does limit how much memory the OS kernel can use, which if is too little, excessive paging will occur.
    Mark
  10. gurucb New Member

    Pages / sec counter alone may not be good value to deduce if paging is occuring on server. If page / sec is high is Available MBytes also fall down as a whole on server. If we look at working set size, is it getting trimmed for sql server or other apps. If all of them are true we may say there is paging occuring on server. The hotfix that you were talking about sql server not able to see entire memory :
    http://www.microsoft.com/downloads/...47-3f1f-48b8-9e4c-dc32875e1961&displaylang=en

    It halves memory seen by sql server so if you have 16 Gigs and you see only 8 Gigs then this may be the issue. /3GB thougg is not a problem, if enabled any memory above 16Gigs will not be visible to Operating system and thus to sql server.

    But as per my experience(may not be true..) /3GB on system with 12 GB to 16 GB is problem as well. In your case I would not turn on /3GB.
    <your latest post>
    Posted - 10/31/2006 : 10:26:10
    --------------------------------------------------------------------------------

    I have the /3gb switch on now, but the Target Server Memory and Total Server memory only report back 2.7 gigs. I saw a hot fix from MS that fixed a problem with AWE not seeing all available memory on a SQL server 2000 with SP 4 installed. Is anyone familiar with this?

    The server is 2003 Enterprise SP1

    <your latest post>


    But if you have 16 GB why is sql server target memory only 2.7 Gigs.
    Is AWE set on SQL Server.
    Is Max server memory set on SQL Server.
    Unless AWE is set SQL Server will not see memory and If max server is not set after enabling AWE we will see SQL consuming entire memory on box leaving nothing an that can casue paging as we are discussing here.

    Hope this helps.

Share This Page