Memory configuration and using AWE /3GB /USERVA in SQL Servers 2005/2008 in 64 bit and 32 bit installations

In this post we will discuss various memory configurations in 64 bit and 32 bit installations of SQL Server:

In 32 bit OS systems, AWE was used for addressing the issue to access the memory above the 32 bit process limitation. It also enables SQL Server to lock pages in memory.
Servers that are x86 or 32 bit have a VAS region that is 2^32 in size or roughly speaking 4 GB. This is by default split 50/50 into kernel mode and user mode VAS regions. To work around this limitation, the /3GB and /USERVA boot.ini switches can be used to change the user/kernel mode proportions from 50/50 to 75/25 (/3GB) or a user defined proportion (/USERVA) to allow SQL Server to address more memory for its buffer pool.

For example, a 32bit server with 4GB RAM, the /3GB boot.ini switch, and a max server memory configuration of 2048MB the maximum size of the BPool would be 2048MB:

3072MB (3GB) user VAS – 384MB = 2688MB available VAS (384MB is VAS Reservation for 32 bit 2 core system which is (256*512KB) + 256MB)
4096MB (4GB) Physical Memory > 2688MB available VAS
2688MB available VAS > 2048MB max server memory configuration

However, when AWE is enabled the BPool maximum size isn’t restricted by the available user mode VAS. Instead the maximum size of the BPool is based on the physical memory on the server or the max server memory configuration setting, whichever is smaller.
Servers that are x64/IA64 or 64 bit have a VAS region that is 2^64 in size or which is incredebily large number as per todays memory needs.
In 64 bit OS, the above limitation of process is no more applicable. But the second advantages of making SQL server Service account locking pages in memory is the very useful if you encounter any error messages like

significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 31184, committed (KB): 1176140, memory utilization: 2%.

Note:VAS Reservation in 64 bit OS will be (512*2048KB) + 256MB or 1280MB because the default StackSize is 2048KB or 2MB.

The AWE options in sp_configure and SSMS are ignored when you are using the 64 bit OS but if you give SQL Server Service account lock pages in Memory privileges gpedit.msc and you are using Enterprise Edition, then SQL Server will use AWE APIs to allocate memory.
For 64 bit servers, there is no need to use AWE to allocate memory above 3GB for SQL Server since the user mode VAS is 8TB there is always ample VAS to utilize all of the physical memory available on the server. However, the AWE mechanism for allocating memory is still useful and available on 64 bit servers. It is used whenever the the Lock Pages in Memory security right has been granted to the SQL Server Service Account and can improve the stability and performance of a 64 bit system.
To assign the Lock pages in memory user right, follow these steps:
1.Click Start, click Run, type gpedit.msc, and then click OK.
2.Expand Computer Configuration, and then expand Windows Settings.
3.Expand Security Settings, and then expand Local Policies.
4.Click User Rights Assignment, and then double-click Lock pages in memory.
5.In the Local Security Policy Setting dialog box, click Add User or Group.
6.In the Select Users or Groups dialog box, add the account that has permission to run the Sqlservr.exe file, and then click OK.
7.Close the Group Policy dialog box.
8.Restart the SQL Server service.

After you assign the Lock pages in memory user right and you restart the SQL Server service, the Windows operating system no longer pages out the buffer pool memory within the SQL Server process. However, the Windows operating system can still page out the nonbuffer pool memory within the SQL Server process.

You can validate that the user right is used by the instance of SQL Server by making sure that the following message is written in the SQL Server Error Log at startup:
Using locked pages for buffer pool

Please refer below link for more information

Leave a Reply

Your email address will not be published. Required fields are marked *