Post reply

How to configure sql server 2008 memory

  • March 20, 2009 at 10:52 am

    #384175

    Hello Room,

    I try to google and seach how to configure the 3GB, but I couldn’t find.

    The Data Center has the following configuration:

    •Window Server 2003 Enterprise Edition SP2 with 8GB Ram

    - RAID 1 (1TB)

    - RAID 1 + 0 (3 TB)

    - RAID 1 (1 TB)

    •SQL Server 2008

    Questions:

    - What parameter should I change/modify Boot.ini?

    Please advise and also let me know?

    Thanks in advance.

  • March 20, 2009 at 2:59 pm

    #963814

    There are pro and con on 3GB. I am little confused.

    Should I change my boot.ini in my case scenario which 8GB Ram on Window Server 2003 Enterprise edition?

    multi(0) disk (0) rdisk (0) partition(1)\ WINDOWS="Windows Server 2003, Standard" / noexecute = optout / fastdetect / 3GB

    There are pro and con on 3GB. I am little confused.

  • March 20, 2009 at 7:52 pm

    #963894

    Check:-

    How to Set the /3GB Startup Switch in Windows

    http://technet.microsoft.com/en-us/library/bb124810.aspx

    MJ

  • March 21, 2009 at 1:54 am

    #963927

    Post adding /3GB in boot.ini server needs to be rebooted

    Then u have to decide how much u r planning to provide for SQL server

    For example : Total physical memory 8GB

    SQL memory allocation : 6.5GB - 6656 MB

    OS memory allocation :1.5 GB

    Check the SQL server startup account and add it to lock pages in memory in secpol.msc

    then run these queries in SQL management studio

    Use master

    go

    sp_configure 'AWE Enabled',1

    go

    reconfigure with override

    go

    Restart SQL server services

    Use master

    go

    sp_configure 'Max memory settings(MB)',6656

    go

    reconfigure with override

    go

  • March 21, 2009 at 1:56 am

    #963928

    this example is for SQL server 2005

  • March 21, 2009 at 5:16 pm

    #964035

    No different steps for SQL 2008.

    MJ

  • March 23, 2009 at 1:16 am

    #964260

    I assume the version of SQL Server is 32bit, otherwise no need to put anything into boot.ini.

    If the amount of RAM is less then 16GB than should put 3GB switch.

  • March 23, 2009 at 8:29 am

    #964454

    Many thanks for your help and advise.

    Yes. I have the SQL Server 2008 32 bit.

    The RAM is 8GB. Some advised me not to configure/change the AWE settings.

    The forums advised me to add 3GB in boot.ini.

    What will the SQL Server 2008 and O/S manage itself to take advantage on memory allocations?

    What is the max memory allocation for SQL Server 2008?

    What is the max memory allocation for o/S?

    I read the SQL books by Ross Mistry and try to understand. If I leave the AWE default settings and only added 3GB on boot.ini.

    Will the SQL Server SQL 2008 utilize itself to take advantage of the 8 GB?

    Please advise and help.

  • March 23, 2009 at 8:51 am

    #964473

    http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx

  • March 23, 2009 at 9:27 am

    #964519

    Performing the following step of configurations:

    1. use master

    go

    exec sp_configure 'AWE enable', 1

    go

    exec reconfigure with override

    go

    use master

    go

    exec sp_configure 'Max memory setting(MB); 6656

    go

    exec reconfigure override

    go

    2. Boot.ini

    multi(0) disk (0) rdisk (0) partition(1)\ WINDOWS="Windows Server 2003, Standard" / noexecute = optout / fastdetect / 3GB

    3. Reboot the 0/S server and restart the SQL Server 2008 database instance services.

    Will the SQL Server 2008 and O/S take the maximum efficient memory allocation based on the above steps?

  • March 23, 2009 at 12:32 pm

    #964675

    Max memory setting(MB)

    ...is not a valid setting.

    Use "max server memory"


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

  • March 23, 2009 at 12:50 pm

    #964687

    Hi Raj,

    Questions:

    The SQL Server 2008 is running on a window account “SQLAdmin”.

    SQLAdmin is the local User and Group account.

    Can you clarify/explain on how to add the “SQLAdmin” to lock pages in memory in secpol.msc?

    Thanks in advance for your help and advise,

    Edwin

  • March 23, 2009 at 1:05 pm

    #964697

    It depends on the OS, but it's a user right in the control panel (system in XP), I think similar in W2K3

  • March 23, 2009 at 1:18 pm

    #964706

    hi Steve,

    Can you please provide the steps on windows server 2003 environment?

    Many thanks for your help.

    Edwin

  • March 23, 2009 at 1:25 pm

    #964715

    Start->Run secpol.msc

    Expand Local Policies->User Rights Assignment

    Double Click "Lock Pages in Memory" and add your service account in there.


    Check Your SQL Servers Quickly and Easily
    www.sqlcopilot.com

Viewing 15 posts - 1 through 15 (of 18 total)

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