Today I received following question:
We will be running Windows Server 2003 x64 with SQL Server 2005 x64. I would think AWE would not come into the picture in this scenario.
I'm trying to find out more about SQL Server 2005 memory management on x64 systems using x64 versions of the OS and SQL Server.
What role if any does AWE play in this scenario?
In the post http://blogs.msdn.com/slavao/archive/2005/08/31/458545.aspx I did indicate that on 64 bit platform we recommend to enable SQL Server to use locked pages in memory. You can do it by giving SQL Server's account a right to lock pages in memory. By giving this right to SQL Server you enable SQL Server to allocate and hold on to physical memory so that OS can't reclaim the memory back through paging - swapping SQL Server to disk. Even though we have plenty of safeguards inside of the server, still the swapping in some cases can bring SQL Server's performance to its knees.
When running with locked pages enabled, SQL Server monitors box wide memory state and does release memory back to OS in the case of pressure. The key here is that internally SQL Server makes its own decision about what memory can be freed. Internal decision making significantly helps SQL Server to avoid performance degradation in cases of box wide memory pressure. Keep in mind that SQL Server won't release its memory below 'min server memory' specified through sp_configure.
So where does AWE mechanism comes in here? On 64 bit platforms when running with lock pages privilege enabled, we use OS's AWE mechanism http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dngenlib/html/awewindata.asp to allocate physical memory. However we map allocated memory only once since we have plenty of VAS, virtual address space. On 64 bit platform to make use of locked pages you don't have to turn on AWE through sp_configure.
Hope this answers the question!