Issue:
When you look at the amount of memory that the sqlservr.exe process has allocated it shows about 1.7GB of memory used.
Cause:
The default settings of Windows 2003 and SQL Server 2005 limit the amount of memory allocated for the SQL process to 2GB of RAM.
The default Windows 2003 configuration allocates 2GB of RAM to the Virtual Address Space of all processes, in this case SQL Server. The default setting of SQL Server does not allow it process to access memory outside of its Virtual Address Space.
Solution:
To configure the SQL Server to use more than 2GB of RAM do the following. The information provided only applies to 32bit versions of Windows 2003.
Windows 2003 Standard Edition (4GB Max RAM)
1) Add the following switch to the Boot.ini file
a. /3GB
i. This will allow SQL Server to allocate 3GB of RAM to its Virtual Address Space
2) Configure the File and Printer Sharing for Microsoft Networks setting to
a. Maximize data throughput for network applications
3) Reboot Server
Windows 2003 Enterprise Edition (32GB Max RAM)
1) Add the following switches to the Boot.ini file
a. /3GB
i. This will allow SQL Server to allocate 3GB of RAM to its Virtual Address Space
ii. Can only be used on servers with 16GB or less of RAM. See More Information section below for more details.
b. /PAE
i. This allows the OS to recognize more than 4GB or RAM.
2) Configure the File and Printer Sharing for Microsoft Networks setting to
a. Maximize data throughput for network applications
3) Configure Lock Pages in Memory user right
a. Configure the appropriate user with the Lock Pages in Memory user right
b. If the Local System account is configured to start the service add the SYSTEM account, as shown below. Otherwise, add the user account that is configured to start the service.
4) Enable AWE in SQL Server.
a. This allows SQL Server to reference memory outside its Virtual Address Space, essentially allowing SQL Server to reference all the memory that the operating system recognizes (the amount of RAM shown in the System Properties General Tab).
b. You can also use the following SQL Commands to enable AWE
Use master
Exec sp_configure 'awe enabled', '1'
RECONFIGURE
5) Reboot Server
If you successfully configure AWE, you will see the following message in the SQL Server Log.
After AWE is enabled you may find that values in Task Manager do not report as expected. While all your memory is now being used (Available memory is very low or dropping), the sqlservr.exe process does not show high memory utilization. You will also notice that the PF Usage graph is almost equal to the amount of memory in the system. All of this is normal, and is happening because SQL Server pages that were normally being paged to disk are now locked in memory and not paged to disk.
If you are concerned about the amount of memory that SQL Server is now using, you should configure the Maximum server memory (in MB): setting in the SQL Server properties to the desired value.
More Information:
How to configure SQL Server to use more than 2 GB of physical memory
http://blogs.msdn.com/chadboyd/archive/2007/03/24/pae-and-3gb-and-awe-oh-my.aspx
Large memory support is available in Windows Server 2003 and in Windows 2000
Using the 3GB and PAE switches when running SQL 2005 causes issues
The memory architecture of the Windows operating system allocates 4GB of RAM to every process. By default this is allocated as 2GB to OS and 2GB to the process. The /3GB switch allows memory to be allocated as 1GB to the OS and 3GB to the process. The entire 4GB is called a Virtual Address Space (VAS), and the two parts are referred to as the System VAS and the User VAS.
Furthermore, by default the memory architecture of Windows operating system is limited to 4GB total physical RAM. However, by adding the /PAE switch to the Boot.ini the Enterprise and Data Center versions of Windows 2003, the OS can access more than 4GB physical memory. Windows 2003 Standard Edition is limited to 4GB of RAM and does not support Physical Address Extensions (PAE).
AWE allows a process to access memory outside of its allocated Virtual Address Space (the User VAS). Thus, if the system has PAE enabled and 8GB of RAM, with AWE enable the SQL Server process can access all 8GB of RAM.
When the /3GB switch is used in conjunction with the /PAE switch the operating system does not use any memory in excess of 16GB. This behavior is caused by kernel virtual memory space considerations. Thus, if the system restarts with the /3GB entry in the Boot.ini file, and the system has more than 16GB of physical memory, the additional physical random access memory (RAM) is not used by the operating system. Restarting the computer without the /3GB switch enables the use of all the physical memory.