How to configure sql server 2008 memory

  • Ed7

    SSCrazy Eights

    Points: 8352

    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.

  • Ed7

    SSCrazy Eights

    Points: 8352

    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.

  • MANU-J.

    SSC-Dedicated

    Points: 31126

    Check:-

    How to Set the /3GB Startup Switch in Windows

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

    MJ

  • rajganesh.dba

    SSCrazy

    Points: 2458

    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

  • rajganesh.dba

    SSCrazy

    Points: 2458

    this example is for SQL server 2005

  • MANU-J.

    SSC-Dedicated

    Points: 31126

    No different steps for SQL 2008.

    MJ

  • Roust_m

    SSCoach

    Points: 17364

    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.

  • Ed7

    SSCrazy Eights

    Points: 8352

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715373

  • Ed7

    SSCrazy Eights

    Points: 8352

    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?

  • Richard Fryar

    Hall of Fame

    Points: 3457

    Max memory setting(MB)

    ...is not a valid setting.

    Use "max server memory"

  • Ed7

    SSCrazy Eights

    Points: 8352

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715373

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

  • Ed7

    SSCrazy Eights

    Points: 8352

    hi Steve,

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

    Many thanks for your help.

    Edwin

  • Richard Fryar

    Hall of Fame

    Points: 3457

    Start->Run secpol.msc

    Expand Local Policies->User Rights Assignment

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

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

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