Article ID: 274750
Article Last Modified on 12/23/2005
APPLIES TO
- Microsoft SQL Server 2000 Enterprise Edition
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2005 Enterprise Edition
This article was previously published under Q274750
SUMMARY
This article describes how to configure SQL Server to use more than 2 GB of physical memory.
Microsoft SQL Server 7.0 and Microsoft SQL Server 2000 dynamically acquire and free memory as needed. When you run multiple instances of SQL Server on a computer, each instance dynamically acquires and frees memory to adjust for changes in the workload of the instance.
SQL Server 2000 Enterprise Edition introduces support for the use of Microsoft Windows 2000 Address Windowing Extensions (AWE) to address approximately 8 GB of memory for instances that run on Microsoft Windows 2000 Advanced Server, and approximately 32 GB for instances that run on Microsoft Windows 2000 Datacenter. With AWE, SQL Server can reserve memory that is not in use for other applications and the operating system. Each instance that uses this memory; however, must statically allocate the memory it needs. SQL Server can only use this AWE allocated memory for the data cache and not for executables, drivers, DLLs, and so forth. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
283037 Large memory support is available in Windows 2000 and Windows Server 2003
The extended memory size option is available only for Microsoft SQL Server 7.0, Enterprise Edition on the operating system and hardware that supports the Enterprise Memory Architecture (EMA) feature. For more information about how to configure your system to enable the EMA feature on a particular system configuration, refer to your Microsoft Windows NT documentation.
Some system vendors may provide products for Microsoft Windows NT, version 4.0 or later, so that SQL Server 7.0, Enterprise Edition can use the extended memory size option. On Intel platforms, SQL Server 7.0 can use a feature known as PSE36. On Alpha platforms, the feature that allows use of the extended memory option is Very Large Memory (VLM). For more information about the availability, installation, and configuration of these products, contact your system vendor.
Note: To use Address Windowing Extensions (AWE) memory, you must run the SQL Server 2000 database engine under a Windows account that has been assigned the Windows lock pages in memory administrative credentials.
Note If you are using SQL Server 2005 to configure memory to use more than 2 GB of physical, see the following topics in SQL Server 2005 Books Online:
- Memory Architecture
- Server Memory Options
- Using AWE
- Enabling Memory Support for Over 4 Gb of Physical Memory
- Enabling AWE Memory for SQL Server
Support for operating system versions
Windows 2000 Server
SQL Server 2000
Normally, both the SQL Server 2000 Enterprise Edition and SQL Server 2000 Developer Edition can use up to 2 GB of physical memory. With the use of the AWE enable option, SQL Server can use up to 4 GB of physical memory.
Note You cannot allocate more than 4 GB of physical memory to an application on Windows 2000 Server because Physical Address Extension (PAE) is not available on Microsoft Windows 2000 Server. Also, you cannot use the 3 GB switch in the Boot.ini file with Windows 2000 Server; however, you can use the 3 GB switch in the Boot.ini file with Microsoft Windows 2000 Advanced Server or Microsoft Windows Datacenter versions. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
291988 A description of the 4 GB RAM Tuning feature and the Physical Address Extension switch
SQL Server 7.0
SQL Server 7.0 versions can use up to 2 GB of physical memory.
Note The extended memory option in SQL Server 7.0 is not available on Windows 2000.
'
Windows 2000 Advanced Server or Windows 2000 Datacenter or Windows Server 2003
The maximum amount of physical memory addressable by a 32-bit addressing mode is 4 GB. All processors based on the IA-32 architecture that begin with the Intel Pentium Pro, support a new 36-bit physical addressing mode known as Physical Address Extension (PAE). PAE allows up to 8 GB of physical memory on Windows 2000 Advanced Server and up to 32 GB of physical memory on Windows 2000 Datacenter Server. This is because the tested memory limit on Windows 2000 Datacenter Server is 32 GB. The PAE mode kernel requires an Intel architecture processor, Pentium Pro, or later and either Windows 2000 Advanced Server or Windows 2000 Datacenter. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
268230 Scaling out versus scaling up with Intel Physical Addressing Extensions (PAE)
Note The maximum amount of memory that can be supported on Windows Server 2003 is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of physical RAM. Windows Server 2003 Datacenter Edition supports 64 GB of physical RAM by using the Physical Address Extensions (PAE) feature. You can use the 3 GB switch that is in the Boot.ini file with Microsoft Windows Server 2003, Microsoft Windows Server 2003 Enterprise Edition, or with Microsoft Windows Server 2003 Datacenter Edition.
SQL Server 2000
Both SQL Server 2000 Enterprise and SQL Server 2000 Developer Editions can use the following options:
Use of the /PAE switch in the Boot.ini and the AWE enable option in SQL Server allows SQL Server 2000 to utilize more than 4 GB memory. Without the /PAE switch SQL Server can only utilize up to 3 GB of memory.
Note To allow AWE to use the memory range above 16 GB on Windows 2000 Data Center, make sure that the /3GB switch is not in the Boot.ini file. If the /3GB switch is in the Boot.ini file, Windows 2000 may not be able to address any memory above 16 GB correctly.
When you allocate SQL Server AWE memory on a 32 GB system, Windows 2000 may require at least 1 GB memory to manage AWE.
back to the topExample
The following example shows how to enable AWE and configure a limit of 6 GB for the max server memory option:
sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'awe enabled', 1 RECONFIGURE GO sp_configure 'max server memory', 6144 RECONFIGURE GO
- -or-Use of the /3GB switch in the Boot.ini file allows SQL Server 2000 to use up to 3 GB of available memory.
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini in order to address up to 3 GB of available memory.
Note The extended memory option in SQL Server 7.0 is not available on Windows 2000 Advanced Server or Windows Datacenter.
Microsoft Windows NT 4.0 Enterprise Edition
SQL Server 2000
SQL Server 2000 Enterprise and Developer Edition requires the /3GB switch in the Boot.ini file in order to address up to 3 GB of available memory.
Note Windows NT 4.0 Enterprise Edition does not support the AWE memory architecture model so AWE support is not available.
SQL Server 7.0
SQL Server 7.0 Enterprise Edition requires the /3GB switch in the Boot.ini file in order to address up to 3 GB of available memory.
back to the top
REFERENCES
For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
170756 Available switch options for Windows NT Boot.ini file
171793 Information on application use of 4GT RAM tuning
268363 Intel Physical Addressing Extensions (PAE) in Windows 2000
292934 Windows 2000 Datacenter Server does not locate memory greater than 16 GB
160606 Performance enhancements for SQL Server under Windows NT
See the "Managing AWE Memory" topic and the "Running Multiple Instances and Using Failover Clustering" topic in SQL Server 2000 Books Online.
back to the top
Keywords: kbhowtomaster KB274750
