JSI Tip 10136. How to configure SQL Server to use more than 2 GB of physical memory?

Microsoft Knowledge Base Article 274750 contains the following 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

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.