Two Common Mistakes to Avoid When Handling Large Amounts of SQL Server Memory

For starters, don't assume that Address Windowing Extensions (AWE) is working in SQL Server unless you verify that it's enabled and using the amount of memory you expect. I've seen more than a dozen customer installations in the last year in which SQL Server wasn't using more than the default 2GB user-process address space even though the server had substantially more than 4GB of memory and the customer assumed that SQL Server was using the full amount of memory. The most common reasons this might happen follow: the 'AWE enabled' option isn't set in SQL Server, the Microsoft Windows account that the SQL Server service is running under doesn't have the lock pages in memory permission granted (you'll see an explicit message in the SQL Server error log when SQL Server starts if this problem occurs), or the /PAE switch isn't set in the boot.ini file. If AWE is correctly enabled, you'll see the message "Address Windowing Extensions enabled" in the SQL Server error log. AWE isn't enabled if you don't see that confirmation. See my article, "Using SQL Server with Large Amounts of Memory," for more information about this topic.

Also, make sure that you set the max server memory option by using sp_configure. If you enable the AWE option in SQL Server and fail to set an upper limit for max server memory, SQL Server will acquire most of the server's memory and leave as little as 128MB for the OS and all other tasks, which will inevitably lead to massive amounts of paging and dramatically degrade system performance. See the Microsoft article, "How to configure memory for more than 2 GB in SQL Server" at http://support.microsoft.com /default.aspx?scid=kb;en-us;274750 for a more in-depth look at this topic.