If you find that you are running into a memory bottleneck, and assuming you have the money to spend, SQL Server 2000 Enterprise Edition can support up to 64GB of RAM. How much RAM SQL Server 2000 Enterprise Edition can use depends on which version of Windows 2000 or Windows 2003 you are using and how much RAM your server can support. Assuming your server can handle it, SQL Server 2000 Enterprise Edition supports up to 8GB under Windows Advanced Server, and up to 64GB under Windows Data Center.
Normally, 32-bit CPUs, such as the Pentium family of processors, can only support up to 4GB of RAM because of its limited address space. To get around this limitation, SQL Server 2000 Enterprise Edition supports a feature called AWE (Address Windowing Extensions), that allows up to 64GB of RAM to be addressed.
Assuming you configure the appropriate hardware and software, AWE support is not turned automatically on, you have to do this step manually. To turn AWE support on, you must change the “awe enabled” advanced SQL Server 2000 option from 0 to 1. For example, to turn on AWE support:
SP_CONFIGURE ‘show advanced options’, 1
RECONFIGURE
GO
SP_CONFIGURE ‘awe enabled’, 1
RECONFIGURE
GO
AWE memory cannot be dynamically managed, like memory is normally managed in SQL Server. This means that SQL Server will automatically grab all the RAM it can when it starts (except for about 128MB, which is reserved for the operating system), but it will not release any of this RAM until SQL Server is turned off. If your server is a dedicated SQL Server, then this might be OK. But if you are running other software on the server, or are running multiple instances of SQL Server, then you must specify the maximum amount of RAM that SQL Server can grab when it is started. This can be done using the “max server memory” configuration option. If you change this setting, you will have to stop and start the mssqlserver service in order for the new setting to take affect.
To set the maximum amount of memory that AWE memory can access, you can use SQL Server’s “max server memory” configuration option. For example:
SP_CONFIGURE ‘max server memory’, 4096
RECONFIGURE
GO
In the above example, we are telling SQL Server to only use 4GB of RAM, leaving any other RAM available in the server free for other applications.
While multiple instances of SQL Server can be used with AWE memory, you probably won’t want to, as it can be a headache to manage. In fact, running multiple instances of SQL Server in AWE memory defeats the purpose of more RAM in the first place. Generally, your goal of using AWE memory should be to support a single, very large instance of SQL Server, not lots of smaller instances running on a single server.
Assuming you have the budget, adding large amounts of RAM to your server can greatly speed up many databases. [7.0, 2000] Updated 1-2-2004