The server is Windows Server 2003 SP2 (32-bit), running SQL Server 2005 SP3 (32-bit). I believe the server has 4 GB of physical RAM. The machine has crashed twice in the last week and it is a production machine. From what I understand, it has AWE enabled with a maximum memory limit of 6041 MB. I never heard of AWE, but I thought this was a little suspicious and suspected thrashing was the reason the machine crashed.
(FYI, AWE is enabled and the /3GB switch is present in the boot.ini file).
I devoted some time today to SQL Server's memory architecture, and I don't think I quite grasp the concept of AWE.
So, Address Windows Extension (AWE) is relative to physical memory, not virtual memory (paging). If the machine only has 4GB of physical RAM, how can it use more than that, if it's not touching the paging file?
Or, is the server not applying AWE correctly? That is, say I have 8 GB RAM, but Windows 2003 32-bit user space can only access 2 GB (3GB with the switch). But with AWE enabled (and page locking in memory checked), I can use an additional 4GB of RAM for SQL Server 2005 (32-bit), on top of the 4GB for Windows (3GB Windows user space + 1GB Windows kernel space), IF AND ONLY IF I have the physical RAM to support it.
I'm really confused and need some guidance with this matter. I'm trying to determine if I understand AWE, how it works, and if it's contributing to the machine crashing.
Thank you for any help.
If you have 4 Gigs of RAM, and AWE enabled you'll want to setup the max server memory to no more than 3 Gigs of RAM, probably less depending on what else is installed on the server.
You'll want to have the 3GB switch enabled as well as having AWE enabled in SQL Server.