sundar

AWE allow 32-bit operating systems to access large amounts of memory (sql

Discussion created by sundar on Oct 23, 2009
Latest reply on Aug 24, 2011 by sundar
Hi Guys,  i found an interesting article about sql server 2005(Hope many of  you  have already know).This is to be tested and to be  shared  with their respective DBA's.It is nothing specific to clarity application.i want to share this for the users  who are having performance issues  with sql server 2005 or higher,they can have an look.  Using AWE-mapped Memory with Windows Server 2003Address Windowing Extensions (AWE) allow 32-bit operating systems to access large amounts of memory.SQL Server 2005 supports dynamic allocation of AWE memory on Windows Server 2003. During startup, SQL Server reserves only a small portion of
AWE-mapped memory. As additional AWE-mapped memory is required, the operating system dynamically allocates it to SQL Server. Similarly, if fewer
resources are required, SQL Server can return AWE-mapped memory to the operating system for use by other processes or applications.
 The amount of physical memory supported has increased with the introduction of the Windows Server 2003 family. The physical memory accessible by
AWE depends on which operating system you are using. The following list provides the maximum physical memory accessible by each Windows Server 2003
operating system at the time of writing.  Windows Server 2003, Standard Edition supports physical memory up to 4 GB.
Windows Server 2003, Enterprise Edition supports physical memory up to 32 GB.
Windows Server 2003, Datacenter Edition supports physical memory up to 64 GB.
 1. To make AWE available to an instance of SQL Server 2005, use sp_configure to set the awe enabled option to 1, and then restart SQL Server.
 
2. Set min server memory and max server memory3. Before enabling AWE, you must configure the Lock Pages in Memory policy.  To enable the lock pages in memory option
 1.             On the Start menu, click Run. In the Open box, type gpedit.msc. The Group Policy dialog box opens.2.             On the Group Policy console, expand Computer Configuration, and then expand Windows Settings.3.             Expand Security Settings, and then expand Local Policies.4.             Select the User Rights Assignment folder. The policies will be displayed in the details pane.5.             In the pane, double-click Lock pages in memory.6.             In the Local Security Policy Setting dialog box, click Add.7.             In the Select Users or Groups dialog box, add an account with privileges to run sqlservr.exe.    
 
ExampleThe following example shows how to activate AWE and configure a limit of 1 GB for min server memory and 6 GB for max server memory.
First, configure the Lock Pages in Memory policy for the account used for SQL Server service.Next, configure AWE:  sp_configure 'show advanced options', 1RECONFIGUREGOsp_configure 'awe enabled', 1RECONFIGUREGOAfter SQL Server restarts, the following message is written to the SQL Server error log: "Address Windowing Extensions enabled."Next, configure memory:  sp_configure 'min server memory', 1024RECONFIGUREGOsp_configure 'max server memory', 6144RECONFIGUREGOIn this example, the memory settings direct the buffer pool to dynamically manage AWE-mapped memory between 1 GB and 6 GB. If other applications require
additional memory, SQL Server can release the allocated AWE-mapped memory if it is not needed. In this example, the AWE-mapped memory can only be released
up to 1 GB.Dynamic AWE memory also allows SQL Server to increase memory if additional memory is added to a computer that supports Hot Add Memory. Available in
Windows Server 2003, Enterprise and Datacenter editions, Hot Add Memory allows memory to be added while the computer is running. For example, suppose
SQL Server 2005, running under Windows Server 2003, Enterprise Edition is started on a computer with 16 GB of physical memory. The operating system
is configured to limit applications to 2 GB of virtual memory address space; AWE has been activated on SQL Server. Later, the system administrator
adds 16 GB of memory while the computer is running. SQL Server 2005 immediately recognizes the additional memory, and, if necessary, can
take advantage of it.cheers,sundar  

Outcomes