SQL Server 32bit Memory Settings

Today I had to re-familiarise myself with 32 bit memory settings on a legacy server that just hasn’t been migrated to 64 bit as yet – realistically, I haven’t had to think about 32 bit limitations for nearly five years! So, I dug out my old battered copy of Wrox’s SQL Server 2005 Performance Tuning. I read technical books that I don’t own on Safari Online, but my preference is physical!

So, the 32bit architecture limits the set of addresses the processor can access to 4GB. Each process has a total of 4GB of virtual address space (VAS), and this VAS is split evenly in the middle so the kernel has 2GB and user-mode has 2GB. This split can be moved to give the user mode 3GB by using switches like /3GB or /USERVA on Windows 2003. So, on a SQL Server system with 4GB and no switches, SQL Server will typically use 1.7GB for Total/Target Server, and a few hundred MB for other memory operations like linked servers, extended stored procedures etc.

To get past the 4GB limit, Intel physically extended the address bus to 36bit, which was called Physical Address Extension (PAE). This allows a 32bit operating system to address up to 64GB of RAM, but Windows Server 2003 must be Enterprise Edition, and it must be Service Pack 1 or later. It’s not all plain sailing – SQL Server can only use this extended memory for the buffer cache, so each SQL Server still needs some memory in that first 2 or 3GB for those linked servers etc.

You can use /3GB and PAE together, and Microsoft support this to 16GB… but it’s more complicated. I like simplicity if possible! And using them both together can throttle the system twice as /3GB reduces the non-paged pool whilst PAE doubles the size of each allocation.

For SQL Server, some configuration may be required. To allow SQL Server to access more than the 2GB memory limit (or 3GB if /3GB or /USERVA is configured), a setting called Address Windowing Extensions (AWE) must be enabled. Hand in hand with AWE is a OS privilege called Lock Pages In Memory – this enables SQL Server to hold on to memory if Windows essentially wants memory back. The potential downside is that other applications on a shared box may start paging instead.
Simple guidelines:

– migrate to a 64 bit architecture as soon as possible!
– on 32bit, if you have 4GB of RAM use /3GB if memory is the bottleneck for SQL Server
– on 32bit, if you have more than 4GB of RAM, use /PAE together with AWE.

Leave a Reply

Your email address will not be published. Required fields are marked *