Secrets of Advanced Windowing Extensions: Making Sure Your SQL Server Has "Air to Breathe"

I'd like to put a spotlight on several NAV/SQL matters--things I consider important (maybe picking something from my book "The NAV/SQL Performance Field Guide"), not too detailed but still technical enough ..."NAV/SQL Quickies".

SQL and Memory Madness

"Memory" - or physical RAM - of a computer is something we consider naturally like the air we breathe - it's there, we take it. But just like our air is only breathable with the optimal mixture of Nitrogen, Oxygen & Co. also "Memory" is only usable if configured correctly.

At first it is necessary to understand the technical limitations of computers: a 32bit system could allocate 2³² Bytes, that's 4 Giga Bytes - no more, no less. Out-of-the-box a MS Windows Operating System "splits" this RAM 50:50 - half of the Memory is assigned to the OS, the other half is granted to application.

Hence, any application can only use 2 GB of RAM at maximum - and of course, this also applies to the SQL Server! It is possible to change this standard assignment by enabling the /3GB (usually plus /PAE) switch in the system's boot.ini (or "Boot Data Configuration" in Win2k8) file; once enabled the OS will only use 1 GB and grants 3 GB (maximum) to applications.

Hmm ... so how is it possible to run 32bit machines with 8 GB and more? The trick is done via the SQL Server feature "AWE": Advanced Windowing Extensions. This is a technology which - simplified - locks memory ...