Memory Configuration ! Have I got it wrong ? Pls HELP ! Urgent

  • Hi All

     

     

    I would appreciate any help from you all with regards to this:

     

    I have a SQL 2005 standard installation with windows server 2003 enterprise Operating system

    The server has 7GB of memory available to it, but as I know SQL 2003 standard edition would make use of the total server memory available to it.

     

    Here are the changes that I have made. (I have converted it all to GB for easy reading)

     

    I added the 3GB switch to the boot.ini file

    I enabled AWE

    I set mini server memory to 5GB

    I set max server memory to 6GB

    I turned on Performance monitor and the Target server memory counter  = 6GB, the total server memory is also set to 6GB.

     

    Now after all the changes above, the server seems to run slower and looking at task manager, the PF usage is around 6GB.

     

    Can anyone please confirm that the changes I made were indeed correct and possibly educate me on the configuration and how to make the best use of server memory etc.

     

    I look forward to hearing from you all.

     

    Thanks


    Kindest Regards,

    John Burchel (Trainee Developer)

  • Take a look at this link...

    http://support.microsoft.com/kb/283037

    I believe when I pointed out about AWE I said something about "other switches when more than 4 GB" is present.  I haven't had one of those... you will need to investigate a bit; but I think this goes in the right direction...

    Pay particular attention to the comment below (from that link):

    AWE is a set of application programming interfaces (APIs) to the memory manager functions that enables programs to address more memory than the 4 GB that is available through standard 32-bit addressing. AWE enables programs to reserve physical memory as non-paged memory and then to dynamically map portions of the non-paged memory to the program's working set of memory. This process enables memory-intensive programs, such as large database systems, to reserve large amounts of physical memory for data without having to be paged in and out of a paging file for usage. Instead, the data is swapped in and out of the working set and reserved memory is in excess of the 4 GB range. Additionally, the range of memory in excess of 4 GB is exposed to the memory manager and the AWE functions by PAE. Without PAE, AWE cannot reserve memory in excess of 4 GB.

    The following is an example of a Boot.ini file where the PAE switch has been added:

    [boot loader]

    timeout=30

    default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS

    [operating systems]

    multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE

     

     

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • Hi David

     

    Thanks for the reply, what i am trying to understand now is that what other witches/what have I done wrong.

    I have read the Ms link and I believe I have done what I was supposed to do. Its just happened that operations are now taking longer than when AWE wasnt enabled.

    Please advice.

    Thanks

     

     


    Kindest Regards,

    John Burchel (Trainee Developer)

  • as I read the page, to use AWE on a machine with more than 4 gb you do NOT use the 3gb switch; but the /PAE.  Your post says you have used the 3gb switch.  and that link says it will run slower if PAE is not enabled.

    Without PAE, AWE cannot reserve memory in excess of 4 GB.

    multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE  < NOTE: NO /3GB switch... just /PAE

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • Hi

    Thanks for your reply.

    But by defauly PAE is already enabled by default in windows 2003 server, so as I understand PAE doesn't need to be added to the boot.ini file.

    Please advice

     

     

     


    Kindest Regards,

    John Burchel (Trainee Developer)

  • I like x64 bit. No switches needed. Don't know if your server is capable, but might be an option.

    GE

  • I can only provide the link... seems to me that you aren't reading the MS supplied article that says you must add the switch.  That is just one link... if you do a few googles, as I recall, there is information all over the place.  I don't have a machine with more than 4 GB... and all of my servers are running fine.

    Not sure how much clearer an article can be, from MS:

    "PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory. The following operating systems can use PAE to take advantage of physical memory beyond 4 GB:

    Microsoft Windows 2000 Advanced Server

    Microsoft Windows 2000 Datacenter Server

    Microsoft Windows Server 2003, Enterprise Edition

    Microsoft Windows Server 2003, Datacenter Edition

    To enable PAE, use the /PAE switch in the Boot.ini file."

    Perhaps they aren't correct about their product.  they've been wrong before... and maybe there is conflicting information that leads you to believe otherwise.  If my machine was running slow, I would certainly TRY to follow their advice and set the switch.

    I'm done here.

    Thank-you,
    David Russell
    Any Cloud, Any Database, Oracle since 1982

  • John,

    David is correct in regards to adding the /PAE switch.  This may be contributing to what you are seeing in terms of your paging file usage.  I do disagree; however, with not using the /3GB switch.  You want to use the /3GB switch up to 16 GB of memory.  On systems with 16 GB of memory or more, remove the /3GB switch. 

    So what should you do?  Add both the /PAE and /3GB switches to your boot.ini file, reboot the server, make sure AWE is enabled and re-check your min and max server memory settings.  Let us know the results please.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Thanks for your response, its highly appreciated. I have added the 3GB/PAE to the boot bil, I set min server memory to 1Gb and max to 7GB.

    Yet, the page file usage is around 7GB, I am thinking of reducing the max server memory.

    Please advice

    Thanks


    Kindest Regards,

    John Burchel (Trainee Developer)

  • one thing you need to remember, when setting the memory min and max, you need to leave some memory for the OS as well.  with the 7 gigs you have there, I guess I would allocate no more than 6 gig to SQL, personally, I would go 5 gig - leaving 2 gig for the OS and other processes. 

    my 2 cents

    -- Cory

  • John,

    Cory is correct.  If you have 7 GB of memory in your server, your Max Server Memory setting should be no higher than 6 GB.  If your server is a dedicated SQL Server, I would leave 1 GB of memory for the OS.  If you are running any other applications/processes on the server, I would leave 2 GB of memory for the OS.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Hi

    I think I have managed to figure out what the problem is, I reduced the max server memory to 3GB and the time the query runs has reduces significantly, i.e from 45mins to less than 20mins.

    Although AWE is enabled and there is 8GB physical memory, if i set max server memory to 7GB, the Page file usage goes up to 7GB and everything runs really slow.

    I dont quite understand the link, but on my C:, it has around 4GB free and that is the only drive where the page file is, I am wondering, is there any link between Max server memory, Page file on drives and also performance.

    Also, the C: is the only drive with virtual memory specified.

    Thanks

     

     


    Kindest Regards,

    John Burchel (Trainee Developer)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply