The extended memory size option is available only for Microsoft® SQL Server™, Enterprise Edition running under future versions of Microsoft Windows NT®, Enterprise Edition, on Intel® and Alpha platforms. SQL Server utilizes the Enterprise Memory Architecture (EMA) feature that will be available on these platforms in this future version of Windows NT. For more information about configuring your system to enable the EMA feature on a particular system configuration, see your Windows NT documentation.
In addition to the EMA feature support available on future versions of Windows NT, some system vendors may make available products for Windows NT, version 4.0, Enterprise Edition, that SQL Server, Enterprise Edition can use for the extended memory size option. On Intel platforms, SQL Server can use a feature known as PSE36, and on Alpha platforms, the feature is known as VlmCache. For more information about availability, installation, and configuration of these products, see your system vendor.
The option indicates the number of megabytes (MB) of memory to use as an extended cache in addition to the conventional buffer pool. The maximum value to which that extended memory size can be set depends upon the total physical memory available and the amount of physical memory used by SQL Server conventional memory. For example, on a computer with 8 gigabytes (GB) of memory and 2 GB of SQL Server conventional memory, a reasonable value for extended memory size might be in the range of 5000 through 6000. (Windows NT on the Alpha platform supports up to 2 GB of conventional memory usage for SQL Server.) If the same 8 GB system had 3 GB of conventional memory usage for SQL Server, then the option should be set in the 4000 to 5000 range (Windows NT, Enterprise Edition with the 4GT feature supports, on Intel platforms, up to 3 GB of conventional memory usage for SQL Server).
Vendor specific hardware features limit the amount of memory that the EMA feature can support, and therefore that can be used by SQL Server. For Intel platforms with appropriate hardware support, this limit is 64 GB. For Alpha platforms with appropriate hardware support, this limit is 32 GB.
extended memory size is an advanced option. If you use sp_configure system stored procedure to change the setting, you can change extended memory size only when show advanced options is set to 1. The setting takes effect after stopping and restarting the server.
To set the extended memory size option
RECONFIGURE | sp_configure |
Setting Configuration Options |