Post reply

Windows 2003 server + SQL 2005 Memory Configuration ! Not using 7GB

  • October 17, 2006 at 3:20 am

    #114738

    Hi All,

     

    I have a SQL 2005 standard box with windows 2003 server Enterprise on it.

    the server has 7GB of memory on it, and it also says Physical Address Entension on the properties window.

    At the moment on Task Manager, the memory for SQL is 1.7GB, is there anyway I can determine how much memory SQL 2005 is actually using and also force it to use uto the 7GB we have on the box.

    Any info will be appreciated

    Thanks

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • October 17, 2006 at 11:22 am

    #666300

    Turn on Address Windowing Extensions (AWE) in OS and SQL Server

    The complete process goes like this…

    • On a MS Windows 2003 Server with 4 GB of memory, the boot.ini file must have the ‘/3GB’ parameter added with no other flags.  This technically does NOT turn on ASE; but tells MS SQL Sever to use 3 GB of RAM vs. 2 GB that it will otherwise use.  Machines with more than 4 GB take an additional parameter (/PAE) – which is NOT required for our machines.

    • Once this has been done and the server re-booted, the following changes are made in SQL Server:

    SP_CONFIGURE 'show advanced options', 1

    RECONFIGURE                             

    GO

    SP_CONFIGURE 'awe enabled', 1

    RECONFIGURE

    GO

    • Restart the service(s).
    • Test

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

  • October 17, 2006 at 11:27 am

    #666301

    Since your OS is able to see 7 GB of memory, it means you need not enable PAE. As far as i know, its enabled by default with Windows 2003.

    Rest steps are same as mentioned by David. One additional step will be

    sp_configure 'max server memory', 6144 -- this is 6 GB

    RECONFIGURE with override

    You can check PerfMon and look for SQL Server:Memory Manager object and look for Target server Memory and Total Server Memory counters

    One additional thing.. you need to enable Lock Pages in Memory local policy for SQL Server Startup account,.

     

    Hope this helps

    -B

  • October 18, 2006 at 10:10 am

    #666569

    You may also want to add the following additional configuration parameters:

    sp_configure 'min server memory', 6144

    sp_configure 'set working set', 1       --> needs an OS reboot

     

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 19, 2006 at 9:35 am

    #666840

    "SQL Server 2005 Books Online

    set working set size Option

    This option is still present in the sp_configure stored procedure, but its functionality is unavailable in Microsoft SQL Server 2005. (The setting has no effect.)

    For information on this option, see SQL Server 2000 documentation."

    http://msdn2.microsoft.com/en-us/library/ms189056.aspx

    🙁

  • October 19, 2006 at 10:16 am

    #666854

    Thanks for the update in my education on SQL 2005 Chris !

    There is just so much to try and absorb. The caveats from others I consider gems !

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • October 29, 2006 at 3:47 pm

    #668650

    Hi All,

    Thanks for your reply, really appreciated.

    My boot.ini file looks like this:

    [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 /NoExecute=OptIn

    How will I change it to use the max server memory, in this case 6GB.

    Thanks

     

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • October 30, 2006 at 5:54 am

    #668684

    Hi All,

    I have enabled the 3GB switch and also checked the Performance monitoring log: Target server memory and Total server memory.

    I have also set the Max server memory to 6GB, but on process intensive queries, I noticed the following:

    Target server memory =  Total server memory

    6Gb = 6GB

    On checking the task manager, I can see that PF usage is aroung 6GB, I wasnt sure if this was meant to be normal, as I didnt check the task manager before making the change.

    I look forward to hearing from you all...

    Thanks

     

    Kindest Regards,

    John Burchel (Trainee Developer)

  • October 30, 2006 at 12:35 pm

    #668834

    It appears thet your SQL server is using all of the memory you have allocated to it normally. Usually once you reach the limit of the memory that you have allocated it stays there until a restart. Now if you want to start looking at perfmon in more detail as it relates to SQL Server visit:

    http://www.sql-server-performance.com/

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Viewing 9 posts - 1 through 8 (of 8 total)

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