A while back I was working a DR exercise with a server that was handed down to me. There were 15 databases mirrored from production to the DR server. Half way through failing over the databases I got a SQL out of memory error. The first thing I checked was to see how much RAM the server had. It had 8 gig, had been recently rebooted, and the databases were all relatively small. While checking the server memory and seeing the 8 gig, I also noticed this was a 32bit server and PAE was enabled. Now can you guess what the problem was? That’s right; AWE was not enabled in SQL server. So the OS was set for the kernel to have 2gig RAM and any applications could use the rest. However, without AWE enabled SQL server could not take advantage of that additional virtual address space. I turned on AWE, restarted the SQL service for the change to take effect, and it was smooth sailing from there.
Most everything new these days is 64bit and you don’t have this problem, but not all of us have the luxury of new equipment. If you have a 32bit system with more than 4gig of RAM you need to look into enabling PAE for the OS and AWE for SQL. You should also look into using the /3GB switch, but make sure you research it because it is NOT a one size fits all type of solution. I’ll try to cover the /3GB switch in a future post.