Satheesh Kannan Chandra's Blog

As usual, I have blogged this because I was confused, and I hope that I am trying to get clear about the confusion. ;-)

Enterprise level database instances use databases in massive size and it requires larger RAM to process those data. Most of the SQL Servers are running under 32 bit Windows server operating systems, they can directly address up to 4GB of memory. However, to cater current enterprise level applications SQL Server requires memory more than 4GB.

Suppose if we have 4GB RAM, Operating system gets 2GB and the remaining 2GB allocated to user applications. Hence, (without any memory configuration settings), SQL Server can use upto 2GB RAM. However, by using /3GB option in Boot.ini file, SQL server can use upto 3GB of RAM. The /3GB switch is used to tell SQL Server to take advantage of 3GB out of the base 4GB of RAM that Windows 2000 supports natively.

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.

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.

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.

To configure Windows 2000 (Advanced or Datacenter), you must enter one of the following switches (depending on the size of the physical memory you have in your system) in the boot line of the boot.ini file, and reboot the server (mentioned below)

4 GB RAM --> /3GB
8GB RAM --> /3GB /PAE
16GB RAM --> /3GB /PAE
> 16GB RAM --> /PAE

Be aware, If your server has 16GB or less of RAM, then using the /3GB switch is important. But if your server has more than 16GB of RAM, then you must not use the /3GB switch. The reason for this is because the 1GB of additional RAM provided by adding the /3GB switch is needed by the operating system in order to take advantage of all of the extra AWE memory. In other words, the operating system needs 2GB of RAM itself to mange the AWE memory if your server has more than 16GB of RAM. If 16GB or less of RAM is in a server, then the operating system only needs 1GB of RAM, allowing the other 1GB of RAM for use by SQL Server.

Once we have changed the BOOT.INI option according to your RAM size, now it is time to enable in AWE option in your SQL Server. We can enable the AWE option by using T-SQL.

'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO


One caution about using the "awe enabled" setting is that after turning it on, SQL Server no longer dynamically manages memory. Instead, it takes all of the available RAM (except about 128MB which is left for the operating system). If you want to prevent SQL Server from taking all of the RAM, you must set the "max server memory" option to a figure that limits SQL Server to the amount or RAM you specify. Change the "max server memory" option by using the T-SQL,

sp_configure 'max server memory', 6144
RECONFIGURE
GO

Note:

1. 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.

2. 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

3. When changing the "max server memory" option in SQL Server active-active cluster environment, consider the fail over situation. When an instance fails over to the available node where already an instance is running, the second instance also competes for memory with first instance. If we have 8GB of RAM in each node, then I configure the max memory option to 3GB for each instance. So in case of fail over, both the instances can work comfortably. (3GB for Ist SQL, 3GB for IIns SQL and 1 GB for OS)

4. When you apply SP4 against the AWE enables SQL Server instance, the SQL Server can recognize only half of the amount of memory available. So, if you have 16GB, SQL Server will only use 8GB. Fix is available in this link http://support.microsoft.com/kb/899761

An initiative by one of the leading educational names in technology studies, the Massachusetts Institute of Technology (MIT), has launched its OpenCourseWare project which effectively gives free access to course materials from a number of courses from MIT including Computer Science.

Visit http://ocw.mit.edu/OcwWeb/index.htm for more info.