Sql Server 2005 AWE on Windows 2003 32-bit

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.

18 thoughts on “Sql Server 2005 AWE on Windows 2003 32-bit”

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

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

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

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. 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?

  8. Sandeep,

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

  9. 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!

  10. 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!

  11. hello Haidong,

    I am asking simple Q?

    why the awe is enabling and is it only for 32-bit.

    How to give the space to the large amounts the awe provide?

    If awe enabled, is it their any performance issues?

  12. Hello Haidong

    Explain in detailed about awe i am in confusion?

    How its work?

  13. Hi

    I read all post but still without understand completely.

    Scenario:

    Windows 2003 Enterprise 64 bits
    SQL Server 2005 Standard 64 bits
    AWE enabled
    8 GB RAM
    min memory: 6 GB
    max memory: 6 GB
    boot.ini, not modified
    Lock pages in memory: local service, network, system
    SQL Server (MSSQLSERVER) Log on as: local system account — in services mmc —

    Task Manager, PF of 1,8 GB, but RAM available is more than 6, GB. So I understand that SQL SERVER is not taking all memory that it could take.

    It’s possible with this paremeters be able to SQL SERVER spend more memory? Do I have forgotten something?

    Physical memory, 6 GB available, god, i could not see task manager, its killing me!

    Thanks , excellent post!

  14. Hi Ruben,

    At this point I am not doing much SQL Server work, so my skills are a bit rusty. And I don’t want to lead you down the wrong path.

    I’d suggest you ask the same question at Stackoverflow or Microsoft’s forum. You probably can get better answers there.

    Good luck!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.