Sql-server – Enabling AWE on SQL SERVER 2008

memorysql-server-2008windows-server

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?

Best Answer

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

This is because when 32 bit SQL Server is installed on 32 bit Windows Servers it can see maximum Virtual Address Space(VAS) of 2 GB so its memory consumption under default behavior cannot be more than 2 GB no matter how much RAM is present on windows server.

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

As I can see you want SQL Server to use more memory and for that you are enabling AWE which is correct but first you have to make sure windows server can see more than 4 G of RAM. Again maximum VAS for 32 bit windows system is 2^32 which is 4 GB so any OS process cannot see more than 4 G of RAM. The good thing is you are having Window server 2008 enterprise and you can enable PAE so that windows server can see upto 64 G memory.

To explicitly enable PAE, use the following BCDEdit /set command to set the pae boot entry option: Take windows team help to make the changes.

bcdedit /set [{ID}] pae ForceEnable

IF DEP is enabled, PAE cannot be disabled. Use the following BCDEdit /set commands to disable both DEP and PAE:

bcdedit /set [{ID}] nx AlwaysOff
bcdedit /set [{ID}] pae ForceDisable

Once PAE is enabled windows server can see more than 4 G and then you can enable AWE so the SQL Server data and index pages can see memory more than 2 GB.

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

For 32 bit, maximum memory like I said is 2GB. But when you enable AWE on windows server which can see more than 4 G, ONLY SQL Server data and index pages can use this extra memory not other caches like proc, plan etc.

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

Just test enabling PAE on UAT, enabling AWE is pretty much straight forward and easy.

I suggest you read

PS: Please upgrade to 64 bit ASAP. This will save you lot of hassle moving forward. SQL Server 2016 is now only for 64 bit system so you can see MS is moving away from 32 bit

PPS: SQL Server 2008 is patched to SP2 please patch it to SP4 ASAP.

Related Question