Many DBA's are aware that Enterprise Edition of SQL Server 2000 can use more than 2 GB of memory. For most (albeit...
not all) systems, dedicating more memory to SQL Server translates into better performance; therefore, if the system's performance matters to you, taking advantage of additional memory is a good move. SQL Server can use up to 8 gigabytes of memory while running on Windows 2000 Advanced Server and up to 64 gigabytes with Windows 2000 Data Center Server.
Time and again I have seen environments where DBAs don't know how to take advantage of more than 2 GB of memory. The configuration option that allows SQL Server to use more than 2GB of memory is called "AWE Enabled" -- AWE stands for Address Windowing Extensions.
To enable AWE, you must execute sp_configure system procedure as follows:
EXEC sp_configure 'awe enabled', 1 GO RECONFIGURE WITH OVERRIDE
However, if you simply tweak this option and restart SQL Server service you still might not be taking advantage of all available memory. Even after setting "AWE enabled" on, examining the task manager will show you that SQL Server only uses a small portion of available memory. Most people mistakenly think that SQL Server isn't taking all memory because it does not need it. This simply is NOT true. If you configured SQL Server to use AWE and you still see less than 2 GB of memory usage within the task manager you can expect the following message in the error log:
"Cannot use Address Windowing Extensions because lock memory privilege has not been granted"
In order to take advantage of more than 2GB of memory you MUST allow the Windows account running the SQL Server service to lock pages in memory. To do so you must edit the local security policy, which can be accessed by choosing Start-->Programs-->Administrative Tools-->Local Security Policy. Within the Local Security Settings window you need to navigate to User Rights Assignment folder and double-click the Lock Pages in Memory policy.
This will open a dialog box which lets you add a Windows account to the list of users that are allowed to lock memory pages. Once you have added the SQL Server service account to this list, you need to stop and restart the SQL Server service.
Keep in mind that when the "AWE enabled" option is turned on, SQL Server takes all the memory allocated to it at startup; SQL Server won't wait until it needs more memory even if you have configured it for dynamic memory management. With this in mind you should always set SQL Server to use a fixed amount of memory if you use AWE. Since the operating system needs at least 1GB of memory to run appropriately you should allow SQL Server running on Windows 2000 Advanced Server approximately 6 - 6.5 GB. If you don't run SQL Server on a dedicated server you might wish to save more memory for other applications -- perhaps set SQL Server to use 5GB or less.
Note also that the AWE enabled option has no effect on environments with less than 3 GB of memory -- you can't trick SQL Server into using more memory than available on the server.