• Article

Hot Add Memory

Releases of SQL Server before SQL Server 2005 supported dynamic memory, which allowed SQL Server to automatically adjust memory usage when there was spare memory on the system. However, SQL Server was limited by the amount of memory available at startup. This restriction was removed in SQL Server 2005.

SQL Server now supports Hot Add Memory in Windows Server 2003, which allows users to add physical memory without restarting the server.

Hot Add Memory requires SQL Server Enterprise and is only available for 64-bit SQL Server, and for 32-bit SQL Server when AWE is enabled. Hot Add Memory is not available for 32-bit SQL Server when AWE is not enabled. Hot Add Memory is only available for Windows Server 2003, Enterprise and Datacenter editions. It also requires special hardware supported by the hardware vendor.

Note

To use Hot Add Memory with SQL Server 32-bit, you must start SQL Server by using the -h option. For more information, see Using the SQL Server Service Startup Options.

Note

In Windows Server 2003, PAE is automatically enabled only if the server is using hot add memory devices. In this case, you do not have to use the /PAE switch on a system that is configured to use hot add memory devices. In all other cases, you must use the /PAE switch in the Boot.ini file to take advantage of memory over 4 GB.

Before physical memory can be added to the system, SQL Server must be running in Address Windowing Extensions (AWE) mode and have the startup (-h) switch enabled. For more information about managing AWE, see Managing Memory for Large Databases.

For example, suppose a system administrator is running SQL Server and Windows Server 2003, Enterprise Edition on a computer with 16 GB of physical memory. The operating system is configured to limit applications to 2 GB of virtual memory address space; AWE has been activated on SQL Server and the -h switch enabled during startup. To increase server performance, the system administrator adds another 16 GB of memory. SQL Server recognizes the additional memory immediately, and can begin to use it as necessary, without first requiring a restart of the server.

Note

Removing physical memory from the system still requires restarting the server.