As I was reading one article related to configuring memory on SQL Server instances, I recollected a question asked by one of my team members sometime back. His doubt was a genuine one.
We have 4 Data warehouse servers which have quite a lot of CPUs and huge amount of memory. Those servers run on 32 bit Windows 2003 server operating system and have ~32 GB of RAM. For SQL Server to recognize memory greater than 4 GB, the /PAE switch needs to be enabled in boot.ini file in 32 bit systems. After which AWE will be enabled in SQL Server. But on these servers SQL Server was recognizing all the RAM available but there was no /PAE switch in boot.ini. He was curious to know how SQL Server was recognizing 32 GB of RAM without the /PAE switch.
Even though I had told him that I would check the settings on the server, that had slipped out of my mind. Today I had logged on to one of those servers and I was able to get an answer for his question.
There are two Windows/Hardware level settings even if one of them is enabled /PAE switch need not be enabled in boot.ini. Operating system will automatically enable PAE mode. They are Data Execution Prevention (DEP) and Hot Add Memory devices.
There are two modes of Data Execution Prevention. Hardware enforced DEP and Software enforced DEP. If the Processor supports DEP the Hardware enforced DEP is enabled and /PAE switch need not be enabled. In Software enabled DEP the switch /noexecute=[policy_level] needs to be enabled in boot.ini. The details of DEP and the policy_level switch parameters are described in this article.
If the server supports Hot Add Memory feature, PAE mode is automatically enabled. Hence there is no need of /PAE switch in boot.ini.
Now coming back to the server in question. As mentioned earlier there was no /PAE switch in boot.ini. Here is how the boot.ini looks on that server.
As evident from the screenshot, Software DEP is enabled on that server using the /noexecute=optout switch. This is the reason why the Server is running in PAE mode even without the /PAE switch. Once PAE mode is enabled, AWE needs to be configured in SQL Server so that SQL Server recognzies all the RAM installed on the server.