locked
Incressing the RAM allocation to SQL Server 2000 from 24 GB to 32GB out of 64GB RRS feed

  • Question

  • Hey all,

    I have Windows 2003 server with 64GB RAM which has  SQL Server 2000 SP4 with 24GB allocated,
    If I want allocate 32 GB to SQL Server 2000..it make sence ?..

    Will I get any issues with OS ?
    will SQL Server allows 32GB to allocate ?

    Thanks in advance


    SNIVAS


    • Edited by SNIVAS Wednesday, August 22, 2012 9:07 AM
    Wednesday, August 22, 2012 9:06 AM

Answers

  • Hi,

    You need to do the following:

    1. If you are running 32-bit version of Windows then enable AWE, Because Microsoft Windows 2000/2003 server supports maximum of 64GB memory. If we have installed SQL Server 32 bit version which can support maximum of 3 GB memory on Windows 2000/2003, we can enable AWE feature to use available physical memory of server to improve performance of SQL Server. In simple words, AWE provides memory management functions which lets windows to allow more than 3GB memory to standard 32 bit application.

    This does not apply to 64-bit Windows OS.

    SP_CONFIGURE 'SHOW ADVANCED', 1
    RECONFIGURE
    SP_CONFIGURE 'AWE ENABLED', 1
    RECONFIGURE
    EXEC SP_CONFIGURE N'MIN SERVER MEMORY (MB)', N'min'
    RECONFIGURE WITH OVERRIDE
    EXEC SP_CONFIGURE N'MAX SERVER MEMORY (MB)', N'max'
    RECONFIGURE WITH OVERRIDE
    SP_CONFIGURE 'SHOW ADVANCED', 0
    RECONFIGURE

    2. Before enable AWE u must add the sql server account permission Lock pages in memory.
    (Its only for Enterprise edition )

    3. If ur memory has configured hot-add memory u no need to setup /PAE in boot.ini

    4. Once done the above 3-steps then you must restart your SQL Server.

    Upon Reboot verify the memory changes as follow:

    1. Check the sqlerrorlog

    sp_readerrorlog

    Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

    2. Check the memory


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, August 22, 2012 12:45 PM
    • Marked as answer by Maggie Luo Wednesday, August 29, 2012 12:35 PM
    Wednesday, August 22, 2012 12:45 PM

All replies

  • yes you can

    sp_configure 'show advanced options', 1
    RECONFIGURE
    GO
    sp_configure 'awe enabled', 1
    RECONFIGURE
    GO
    sp_configure 'max server memory', 20000
    RECONFIGURE
    GO
    	


    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, August 22, 2012 10:04 AM
  • For 64 bit application, you can make the changes. Make sure you are leaving atleast 2-4 GB RAM for OS. But for 32 bit application, there are limitations on allocating memory for VAS.

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Wednesday, August 22, 2012 10:47 AM
  • Hi Babu and Latheesh,

    Thanks for replay..

    My Question is

    1.We installed 64 GB RAM in  windows 2003 (32 bit) will Windows use 64GB RAM ?

    2.I have 64GB RAM is Windows 2003 server(32 bit),So that I want allocate 32 GB to SQL Server 2000(32 bit).Will this allocation cause any issues ?

    Thanks in advance


    SNIVAS

    Wednesday, August 22, 2012 11:34 AM
  • Memeory management is totally different from 32 bit than 64 bit. It looks like your system is 32 bit. Though you assign more 4GB, it will not be effective infact useless unless PAE switch is enabled. Please read the below link:

    http://beyondrelational.com/modules/24/syndicated/509/posts/12847/sql-server-32-bit-memory-management.aspx


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!


    • Edited by SQLZealots Wednesday, August 22, 2012 11:48 AM
    Wednesday, August 22, 2012 11:46 AM
  • Hi,

    You need to do the following:

    1. If you are running 32-bit version of Windows then enable AWE, Because Microsoft Windows 2000/2003 server supports maximum of 64GB memory. If we have installed SQL Server 32 bit version which can support maximum of 3 GB memory on Windows 2000/2003, we can enable AWE feature to use available physical memory of server to improve performance of SQL Server. In simple words, AWE provides memory management functions which lets windows to allow more than 3GB memory to standard 32 bit application.

    This does not apply to 64-bit Windows OS.

    SP_CONFIGURE 'SHOW ADVANCED', 1
    RECONFIGURE
    SP_CONFIGURE 'AWE ENABLED', 1
    RECONFIGURE
    EXEC SP_CONFIGURE N'MIN SERVER MEMORY (MB)', N'min'
    RECONFIGURE WITH OVERRIDE
    EXEC SP_CONFIGURE N'MAX SERVER MEMORY (MB)', N'max'
    RECONFIGURE WITH OVERRIDE
    SP_CONFIGURE 'SHOW ADVANCED', 0
    RECONFIGURE

    2. Before enable AWE u must add the sql server account permission Lock pages in memory.
    (Its only for Enterprise edition )

    3. If ur memory has configured hot-add memory u no need to setup /PAE in boot.ini

    4. Once done the above 3-steps then you must restart your SQL Server.

    Upon Reboot verify the memory changes as follow:

    1. Check the sqlerrorlog

    sp_readerrorlog

    Address Windowing Extensions is enabled. This is an informational message only; no user action is required.

    2. Check the memory


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Wednesday, August 22, 2012 12:45 PM
    • Marked as answer by Maggie Luo Wednesday, August 29, 2012 12:35 PM
    Wednesday, August 22, 2012 12:45 PM