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 responses to “Sql Server 2005 AWE on Windows 2003 32-bit”
It’s so helpful. I am able to reconfigur AWE.
Thanks,
Van
You are welcome, Van. Glad it helped.
Do we need to configure Sql Server 2005 to use AWE on 64-bit Windows 2003? Thanks.
Yes, AWE should be turned on, even for 64-bit systems.
Unfortunately, Lock Page in Memory only works for Sql Server 2005 Enterprise Edition.
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.
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.
I realized that the boot.ini is hidden. Thanks.
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.
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.
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.
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?
Sandeep,
In this case, I would put in 64-bit edition of Sql Server.
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!
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!
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?
Hello Haidong
Explain in detailed about awe i am in confusion?
How its work?
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!
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!