This post is part of the monthly community event called T-SQL Tuesday started by Adam Machanic (blog|twitter) and hosted by someone else each month. This month the host is Sankar Reddy (blog|twitter) and the topic is Misconceptions in SQL Server. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.
Next to SQL Server having a Memory Leak, the misconception that you have to enable AWE on 64 bit SQL Servers has to be one of the most prevalent pieces of incorrect advice that I see on the MSDN Forums. This has been blogged about multiple times, yet it continues to be a problem.
Paul Randal: A SQL Server DBA myth a day: (5/30) AWE must be enabled on 64-bit servers
Bob Ward: Fun with Locked Pages, AWE, Task Manager, and the Working Set…
Books Online: awe enable Option
This post was originally started in early March of this year, and I haven't ever gotten back around to finishing it based on a thread on the MSDN Forums that I got involved with where misinterpretations of posts by Bob Ward and Slava Oks were the center of the confusion. (Note here that I said it was someone's misinterpretation, and not incorrect information by Bob or Slava whose posts point out that ‘awe enabled’ is a NoOp in 64bit systems) It seems that the confusion here centers around information that are contained in a couple of blog posts by Slava and Bob where they provide some internals information about memory in SQL Server and use terminology that can be confusing to people just learning about memory internals.
The most common reference that people provide for using ‘awe enabled’ on 64bit servers is Slava Oks blog post Be Aware: Using AWE, locked pages in memory, on 64 bit, where he says:
To some people it comes as a surprise that AWE mechanism is still present and actually could be useful on 64 bit platforms. As you remember the mechanism consists of two parts allocating physical memory and mapping it to the given process's VAS. The advantage of allocation mechanism is that once physical memory is allocated operating system can't reclaim it until either the process is terminated or the process frees memory back to the OS.
Notice that this says AWE mechanism, not enabling AWE. What exactly is the AWE mechanism? It is just a different memory API, AllocateUserPhysicalPages(), used to allocate the memory. On a 64 bit server you enable Lock Pages in Memory to make use of this API instead of using VirtualAlloc() to allocate all of the memory for the buffer pool.
The other common example cited by people is the following comment by Bob Ward on his blog post listed above:
Well….the developers of the product discovered that if they still use the AWE APIs to allocate memory even though it is not really needed, two things would happen:
Thus was born the concept most refer to as “locked pages” for 64bit SQL Server editions.
On more than a few occasions people have pointed to this statement as being proof that you have to use ‘awe enabled’ for a performance boost in 64 bit servers. What always amazes me is that in the same blog post, Bob specifically addresses this:
2. Do I need to use the “awe enabled” sp_configure option on 64bit systems for SQL Server to “lock pages”?
No. In fact, the code for SQL Server for 64bit systems ignores this sp_configure option. It is a “no-op” for 64bit SQL Server systems. You may ask why is this the case if I just told you that AWE APIs are used in 64bit SQL Server systems to “lock pages”?
The answer is based on the purpose for that sp_configure option. The purpose of this sp_configure option on 32bit systems is for the user to “enable” the “AWE” feature, which is I explained above is to extend the ability to reference memory > 4Gb. Now as I mentioned already in order to use the AWE APIs you must have the “Locked Pages in Memory” Privilege. So, when you try to use sp_configure to set ‘awe enabled’ on a 32bit we actually will fail this command if “Locked Pages in Memory” is not set.
You don't have to do a thing with "awe enabled" on 64bit servers to scale memory, and you don't even need to use Lock Pages in Memory. A 64 bit server can utilize the memory available on the server entirely through calls to VirtualAlloc() because it has 8TB (7TB on IA64) of user mode Virtual Address Space within which to allocate the available memory. A 32 bit server doesn't have this ability since it only has 4GB of total Virtual Address Space spit 50/50 into user and kernel modes. The maximum amount of memory that a 32 bit SQL Server can allocate without using AWE is around 1.6GB through VirtualAlloc(), or 2.6GB if the /3GB boot.ini switch has been used (which consequently limits the maximum amount of memory that could be mapped with AWE enabled since it reduces kernel mode address space and the available Page Table Entries for mapping AWE allocated memory).
The performance benefit that Bob and Slava talk about has to do with the fact that when Lock Pages in Memory is set, the memory allocated can't be paged out by the Operating System, a problem that exists when the memory is allocated using VirtualAlloc() only, allowing SQL Server to manage its memory entirely on its own. This is specifically important on NUMA systems as detailed in Slavas blog post. On a 32 bit server having the buffer pool paged is bad, but its impact is low since the buffer pool is 1.6GB. However, on a 64 bit server with 32GB or memory that has 28GB allocated to the buffer pool, the impact of having the memory paged out is very significant. When the SQL Server Service account has the Lock Pages In Memory set in the User Rights Assignment the database engine in Enterprise Edition, or Standard Edition with the correct Service Pack, Cumulative Update, and trace flag 834 set, will use the "AWE Mechanism" to lock the memory allocated for the Buffer Pool.
So there you have it, once again, there is no need to enable AWE on 64bit servers.