Q and A: x64 & AWE mechanism
Today I received following question:
Hello,
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?
Thanks,
In the post https://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 https://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!
Comments
Anonymous
December 12, 2005
I have 2003 x64 and SQL 2005 x64 set up on a AMD64 processor. Physical mem is 8 GB and Pagefile is 12. I have set min SQL memory to 6 GB and Max to 7GB. When doing index rebuilds I can see the mem usage to up to 7 GB but it alway drops under the minimum threshhold. I have 'Lock Pages in Memory' giving to the SQL Account. Is this by design? Why would it drop below the minimum threshold?Anonymous
October 28, 2006
Hello, We have 2003 x64 and SQL 2005 x64 with 16GB , In task manager i always see PF Usage is 15.6GB ? What Should i do, can i enabled AWE in SQL server.Anonymous
November 13, 2006
If I understand you correctly, you are worried about SQL Server process's being paged out. TM 's "PF Usage" counter doesn't show that. In order to find out if SQL Server process is paged out, you need to look at TM's "Mem Usage" counters vs "VM Size". If "Mem Usage" counter is significantly lower than VM Size than a process is paged out. If you see that SQL Server process is paged out you can cope with that by enabling "locked pages in memory". Here is a pointer how to do it http://msdn2.microsoft.com/en-us/library/ms190730.aspxAnonymous
January 23, 2007
Is the "lock pages in memory" assignment necessary if SQL Server is running under the Local System account?Anonymous
October 30, 2008
PingBack from http://joesack.com/WordPress/?p=197Anonymous
January 07, 2009
Yesterday I had a discussion on how to determine the non-buffer pool memory usage (formerly called MemToLeave/MTL)Anonymous
January 21, 2009
PingBack from http://www.keyongtech.com/2229720-memory-allocation-on-64bits-platformAnonymous
March 10, 2009
把和SQLServer内存和AWE相关问题的基本内容整理了一下。 1.SQLServer内存基本概念 在涉及SQLServer内存时有几个比较重要的术语： BufferPool(BP...Anonymous
March 17, 2009
PingBack from http://soci.hu/blog/index.php/2009/03/17/sql-server-lock-pages-in-memory-on-64-bit-platform/Anonymous
May 07, 2009
PingBack from http://www.valinor.co.il/sql/?p=126Anonymous
June 08, 2009
PingBack from http://jointpainreliefs.info/story.php?id=1956