Post reply

Utilized the maximum mem for SQL 2008 and O/S

  • April 6, 2009 at 6:00 pm

    #130535

    Hello Room,

    I have a window Server 2003 Enterprise SP2 OS with 8 GB RAM and SQL Server 2008.

    Hard ware configuration:

    -Quad 2.00 GHz Intel E5405

    -8 GB RAM

    -C Drive 300 GB

    -D Drive 1000 GB

    -E Drive 600 GB

    I have read some discussion on the forum AWE Enabled.

    Also, I posted a question on this but I’m still not clear.

    I would like to configure my SQL Server and O/S take the maximum memory utilizations.

    Can someone check and advise on the following steps:

    1.Edit Boot.ini file

    [Boot Loader]

    Timeout=30

    Default=multi(0)disk(0)rdisk(0)partition(1) \WINDOWS

    [Operating Systems]

    Multi(0) disk(0) rdisk(0) partition(1) \WINDOWS=”Window Server 2003, Standard” /nonexcute=optout /fastdetect /3GB /userva=3030

    2.Use master

    Go

    Exec sp_configure ‘awe enabled’, 1

    Reconfigure with override

    Go

    Exec sp_configure ‘max server memory’, 6656

    Go

    Reconfigure with override

    Go

    3.Run secpol.msc

  • April 6, 2009 at 8:46 pm

    #973042

    Looks pretty reasonable for a 32 bit install -

  • April 7, 2009 at 9:54 am

    #973558

    any reason not to use 64 bits OS and SQL 2008?

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005

  • April 7, 2009 at 10:05 am

    #973576

    Jerry Hung (4/7/2009)

    any reason not to use 64 bits OS and SQL 2008?

    Hi, wht is the diference betwen 32 bit and 64 bit SQL Server

    and 32bit and 64 bit OS? pls give info

  • April 8, 2009 at 12:04 pm

    #974566

    How to determine the SQL Server 2008 memory use after the configuration completed?

    Thanks,

    Edwin

  • April 8, 2009 at 3:09 pm

    #974738

    Achtang (4/7/2009)

    Jerry Hung (4/7/2009)

    any reason not to use 64 bits OS and SQL 2008?

    Hi, wht is the diference betwen 32 bit and 64 bit SQL Server

    and 32bit and 64 bit OS? pls give info

    this explains it pretty simply...

    http://wmug.co.uk/blogs/scambler/archive/2009/02/11/32-bit-x86-vs-64-bit-x64.aspx

  • April 9, 2009 at 10:16 am

    #975198

    At first glance, I think you need to remove the /3GB switch from boot.ini and add the /PAE switch instead.

    Tim White

  • April 9, 2009 at 10:24 am

    #975204

    Also, need to add the "lock Pages in Memory".

    START-RUN , enter - gpedit.msc

    Computer Configuration - Windows Settings - Security Settings –

    -Local Policies - User Rights Assignment

    In the detail, open “Lock Pages In Memory”.

    Click – ADD

    Add the SQL Server start-up acct.

    Tim White

  • April 9, 2009 at 10:42 am

    #975224

    Tim,

    Thanks for you value advice. I went to Microsoft help and support pages and an article indicted that

    “PAE is automatically enabled only if the server is using hot-add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot-add memory devices.”

    Questions:

    How can I find out my hosting server weather use hot-add memory devices?

    Also, some advised to add 3GB switch. I’m not clear on how to setup my SQL Server 2008 and O/S utilized max RAM.

    Thanks,

    Edwin

  • April 9, 2009 at 11:12 am

    #975255

    When to use /3GB and or /PAE is confusing: Some other blogs can explain it better than I can.

    This one's not bad:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55191

    You still need to "lock pages in memory" as described above. (for 32-bit systems)

    Tim White

  • April 9, 2009 at 11:59 am

    #975323

    Tim,

    I updated my boot.ini and performed gpedit.msc.

    Did I need to re-run my t-sql scripts?

    Thanks,

    Edwin

  • April 9, 2009 at 12:41 pm

    #975349

    You do not need to rerun your SQL scripts.

    you can execute sp_configure to look at your current running values.

    If you only see a few lines, then;

    sp_configure 'show advanced options','1'

    reconfigure

    then sp_configure again to see everything

    Tim White

  • April 9, 2009 at 1:01 pm

    #975362

    Tim,

    The SQL logs indicated "Address Windowing Extensions is enabled. This is an information message only; no user action is required"

    How can I confirm that my configuration are completed and fully utilized maximum RAM?

    Thanks,

    Edwin

  • April 9, 2009 at 1:08 pm

    #975368

    if the 'awe enabled' flag in sp_configure is set to '1', you're good-to-go. Also, the start-up log message you already sent is another indication that AWE is being used. There are Perfmon counters you can look at, but I don't remember which ones off the top of my head.

    I use DBCC MEMORYSTATUS

    In the Memoryclerk_sqlbuffer area it will tell you how much AWE is currently being used by SQL Server.

    It may take a while to build up to your max server memory setting.

    Tim White

  • April 9, 2009 at 1:34 pm

    #975388

    Tim,

    Again, thanks a lot for your valuable advice and help.

    The MEMORYCLERK_SQLBUFFERPOOL (Node 0)

    VM Reserved 1594872KB

    VM Commited 8064KB

    AWE Allocated 262144KB

    SM Reserved 0 KB

    SM Commited 0 KB

    SinglePage Allocator 0 KB

    MultiPage Allocator 2096 KB

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

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