1

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?
  • 2
    Where exactly are you checking how much memory SQL Server is using? When are you going to join the rest of us in the 64-bit world? – Aaron Bertrand Jan 3 '17 at 14:35
  • Total Server Memory = Target Server memory = 1.5 GB. I will take some time thinking about your 64-bit World :) – Osama Waly Jan 3 '17 at 14:38
  • I second @AaronBertrand - build a new machine with x64 Windows and x64 SQL Server, then migrate to it. You can still accomplish this using the 2008 versions, if you must stay on such antiquated software, although I would also recommend moving up to something built within the past several years. Depending on your requirements, you may even be able to move to SQL Server 2016 Standard edition and save a ton of cash. – Max Vernon Jan 3 '17 at 14:52
  • 1
    How much data are you actively querying on this server? If you have less data being used than the RAM available then it's not likely to use it all. What's your page life expectancy sitting at? – Rich Benner Jan 3 '17 at 15:11
  • 1
    This situation is temp as the server is planned to be upgraded to 64-bit. what i see here is people are discussing other thing rather than answering the clear questions i've posted. We just want to know the prerequisites of applying the AWE. if you have the answer please share it. – Osama Waly Jan 4 '17 at 7:42
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.