Sql Server 2005 AWE on Windows 2003 32-bit

September 6, 2007 · Filed under SqlServer, Technology

Here are steps to enable AWE so that Sql Server 2005 can use additional memory, if your physical memory is more than 4 GB on 32-bit Windows 2003 box.

1. Add /pae at the end of last line inside c:\boot.ini
2. Run gpedit.msc. On the left hand pane, expand Computer Configuration, expand Windows Settings, expand Security Settings, expand Local Policies, select User Rights Assignment
3. On the right hand pane, find Lock pages in memory and double click, then add your Sql Server startup account into Local Security Policy Setting tab;
4. In Sql Server Management Studio, run:
sp_configure ’show advanced’, 1
reconfigure
sp_configure ‘awe enabled’, 1
reconfigure
Ignore the error message below, if you have it.
Msg 5845, Level 16, State 1, Line 1
Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.
5. Reboot.

14 Comments »

  1. Van Said,

    November 12, 2007 @

    It’s so helpful. I am able to reconfigur AWE.

    Thanks,

    Van

  2. Haidong Ji Said,

    November 12, 2007 @

    You are welcome, Van. Glad it helped.

  3. Eric Said,

    March 12, 2008 @

    Do we need to configure Sql Server 2005 to use AWE on 64-bit Windows 2003? Thanks.

  4. Haidong Ji Said,

    March 12, 2008 @

    Yes, AWE should be turned on, even for 64-bit systems.

    Unfortunately, Lock Page in Memory only works for Sql Server 2005 Enterprise Edition.

  5. Eric Said,

    March 13, 2008 @

    Thanks Haidong. We use Sql Server 2005 Enterprise Edition. There is no boot.ini file so I did from step 2 to step 4. Where can I put /pae in 64-bit systems? Would it work without re-starting the server? (The server needs to run 7/24).
    By the way, I am using the book Professional SQL Server 2005 Administration in which you are one of the authors.
    Thanks a lot.

  6. Eric Said,

    March 13, 2008 @

    Hello Haidong. We are running into a problem with Microsoft SQL Server Enterprise Edition (64-bit) running on Windows Server 2003 R2 Standard x64 Edition. It needs to set execution permission to some system stored procedures (such as ’sp_datatype_info’) for a public user which we use for applications. Do you know why? Do you know there are any issues with Microsoft SQL Server Enterprise Edition (64-bit) running on Windows Server 2003 R2 Standard x64 Edition?
    Thanks.

  7. Eric Said,

    March 15, 2008 @

    I realized that the boot.ini is hidden. Thanks.

  8. Haidong Ji Said,

    March 15, 2008 @

    Eric,

    I don’t know why to your question. It is really strange that you need to give extra system stored procedures permission for a public user. Something does not seem right here.

    64-bit Sql Server Enterprise work very well on Windows 2003 R2 x64 Edition, as long as it is setup correctly. I will write you a separate email.

  9. MJ Said,

    September 19, 2008 @

    Hi, you answered a question for someone, the same question I wanted to ask but could you please provide an explanation for why you would want to set the /PAE and AWE on a x64 Windows Server 2003 Enterprise and a SQL 2005 Server Enterprise? I could really use the help! thank you

    Question: Do we need to configure Sql Server 2005 to use AWE on 64-bit Windows 2003? Thanks.
    Answer: Yes, AWE should be turned on, even for 64-bit systems.
    Unfortunately, Lock Page in Memory only works for Sql Server 2005 Enterprise Edition.

  10. Haidong Ji Said,

    September 19, 2008 @

    Hi MJ,

    For 64-bit systems, PAE is not supported so no need to put that into boot.ini.

    http://www.microsoft.com/whdc/system/platform/server/PAE/PAEdrv.mspx

    Slava Ok wrote a post a few years to address AWE and 64-bit here. I don’t fully understand, but I trust he knows what he’s talking about :)

    http://blogs.msdn.com/slavao/archive/2005/04/29/413425.aspx

    Hope this helps.

  11. Sandeep Said,

    June 25, 2009 @

    HI MJ,
    We have
    SQL Server Standard edition(32 BIt) Installed on Windows 2003 R2 (64BIt) OS. This server has 16Gb RAM but my sql server db is not taking more than 3.6GB.
    DBCC MEMORYSTATUS showing
    VM Reserved 3652068
    VM Committed 3651220
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    I can use upto 12 GB memory for SQL server. Is it possible to assign ? If yes HOw?

  12. Haidong Ji Said,

    June 29, 2009 @

    Sandeep,

    In this case, I would put in 64-bit edition of Sql Server.

  13. Rich Said,

    September 5, 2009 @

    I’m interested in a more thorough answer to Sandeep’s question as we are running the same setup; SQL Server 2005 Enterprise 32-bit on Windows Server 2003 Enterprise R2 SP2 64-bit with 32GB Physical RAM and we CANNOT use 64-bit SQL.
    We HAVE to use 32-bit SQL because we use an OLEDB driver that is only 32-bit, no 64-bit available.

    Question1. Given the provided setup above, if AWE is enabled, will the process actually use more than 2GB/3GB of RAM?

    Question2. If the answer to question 1 is yes, then how can I verify this?
    What specific PerMon counter will clearly display this?
    Thanks!

  14. Haidong Ji Said,

    September 8, 2009 @

    Hi Rich,

    Yes, SQL Server engine will be able to use more than 2GB/3GB of RAM, if

    1. AWE is enabled, and
    2. Lock pages in memory is enabled for the startup account.

    You can verify this by looking at:

    select
    bpool_visible
    from
    sys.dm_os_sys_info

    In Sandeep’s case, it sounded like AWE was enabled, but lock page in memory was not.

    Hope this helps and good luck!

RSS feed for comments on this post · TrackBack URI

Leave a Comment