none
Enabling AWE on SQL SERVER 2008

    Question

  • We have a Server running Windows Server 2008 32-bit (Enterprise Edition) and SQL Server 2008 (SP2) 32-bit (Enterprise Edition) installed.

    The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

    We have to enable the AWE Option on SQL Server but:

    1- Do we have to enable\configure any options on Windows before enabling the option on SQL Server ?

    2- How much memory can be assigned to SQL Server on the Enterprise Windows (Max) ?

    3- Is there any other thing we should take care of before applying since its a production server ?

    Thanks

    Osama Waly

    • Moved by Olaf HelperMVP Sunday, January 15, 2017 12:56 PM Moved from "Database Engine" to a more related forum
    Tuesday, January 3, 2017 2:30 PM

Answers

All replies

  • The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

    Do you see it uses 1.5 GB memory in task manager?

    sp_configure 'show advanced options', 1

    RECONFIGURE

    GO

    sp_configure 'awe enabled', 1

    RECONFIGURE

    GO

    sp_configure 'max server memory', 22528

    RECONFIGURE


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, January 3, 2017 2:34 PM
  • Thanks Uri, i know the configurations but i'm asking about if there are any prerequisites.

    1- Do we have to enable\configure any options on Windows before enabling the option on SQL Server ?

    2- How much memory can be assigned to SQL Server on the Enterprise Windows (Max) ?

    3- Is there any other thing we should take care of before applying since its a production server ?

    Tuesday, January 3, 2017 2:40 PM
  • Changing the memory configuration on old versions of Windows is complicated.  Your versions of SQL and Windows are very old.  And it's likely that changing the settings will not help your performance much.

    In short, what you are attempting is probably not a good idea.  And instead you should start planning for an upgrade at least to a modern 64bit version of Windows, and possibly to a newer 64bit version of SQL Server.

    David


    Microsoft Technology Center - Dallas

    My Blog




    Tuesday, January 3, 2017 3:28 PM
  • Just because SQL is currently only using 1.5 GB memory, doesn't mean it won't use more later as heavy production workload ramps up.

    According to these guidelines, you can set SQL max_server_memory value = 28GB, since your server has 32GB RAM: 
    How much memory does my SQL Server actually need

    Typically, you'll also want to enable LPIM for your SQL Service startup account:
    How to enable the Lock Pages in Memory option

    If the SQL Service startup account does not have local admin rights, you should configure Instant File Initialization:
    How and Why to Enable Instant File Initialization

    If you haven't already applied SP4 update to the SQL instance, you should apply that as well, since it is the only supported (by MS) version of SQL2008, now:
    SQL Server 2008 Service Pack 4 release information

    Hope that helps.


    Phil Streiff, MCDBA, MCITP, MCSA

    • Marked as answer by Osama Waly Wednesday, January 4, 2017 10:10 AM
    Tuesday, January 3, 2017 3:38 PM
  • Thanks , the only requirement is to enable LPIM
    Wednesday, January 4, 2017 10:10 AM
  • You also need to decide whether to set the /3GB switch.

    David


    Microsoft Technology Center - Dallas

    My Blog

    Wednesday, January 4, 2017 1:25 PM
  • Just because SQL is currently only using 1.5 GB memory, doesn't mean it won't use more later as heavy production workload ramps up.

    According to these guidelines, you can set SQL max_server_memory value = 28GB, since your server has 32GB RAM: 
    How much memory does my SQL Server actually need

    Typically, you'll also want to enable LPIM for your SQL Service startup account:
    How to enable the Lock Pages in Memory option

    If the SQL Service startup account does not have local admin rights, you should configure Instant File Initialization:
    How and Why to Enable Instant File Initialization

    If you haven't already applied SP4 update to the SQL instance, you should apply that as well, since it is the only supported (by MS) version of SQL2008, now:
    SQL Server 2008 Service Pack 4 release information

    Hope that helps.


    Phil Streiff, MCDBA, MCITP, MCSA

    Phil,

    I would like to correct here, this is 32 bit SQL Server and on 32 bit OS it can only access 2 G of memory at max no matter what is workload( under default config).

    Setting max server memory unless PAE is enabled is of no use.

    In this case what Osama is asking you need to first enable PAE so that windows server can see more than 4 G and then enable AWE so that SQL Server can use more than 2 G but only for data and index pages.

    Similar thread by Osama

    http://dba.stackexchange.com/questions/159827/enabling-awe-on-sql-server-2008/160036#160036


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, January 9, 2017 1:22 PM
    Moderator