通过

Q and A: Using Lock Pages In memory on 64 bit platform

  • 项目

Q: Hello Slava,  I would like to confirm my understanding that on SQL 2005 64 bit edition it is recommended to grant Lock Pages in Memory right to the SQL account and then turn on the AWE setting. Thanks

A: Yes, we do recommend to turn on Lock pages in memory so that OS doesn't page SQL Server out. However on 64 bit you only need to grant the right "Lock Pages in Memory" to the SQL account for SQL Server to utilize this feature. You do need to to change any of AWE settings through sp_configure.

When you enable Lock Pages In memory, dbcc memorystatus's output will show that AWE mechanism is in use, Why? The reason for this is that for both 32 bit's AWE and 64 bit's Lock Pages we use Windows AWE mechanism described here https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dngenlib/html/awewindata.asp. The only difference is that on 64 bit we only have to map physical pages ones.

Comments

  • Anonymous
    March 01, 2006
    The comment has been removed

  • Anonymous
    March 05, 2006
    This is not true. Physical memory distribution depends on system load. In this configuration you can give 12GB to SQL Server by setting SQL Server min and max memory settings to 12GB using sp_configure. Just keep in mind that SQL Server will allocate memory on demand meaning server will only allocate memory if load present. However once memory is allocated it won't free it below min server memory setting.
    It seems that someone had confusion between managing physical memory and virtual address space. Virtual address space does get split between kernel and user space but not physical. Windows 2003 Server 64 bit edition supports 16TB of VAS. The VAS is split into 8TB for kernel and 8TB for user space correspondingly

  • Anonymous
    July 12, 2006
    The comment has been removed

  • Anonymous
    August 17, 2006
    We are having an issue with SQL2005 64bit memory usage lately.  Our SQL server used to take as much memory as it needed which topped to about 5GB but now it never uses more than 1GB.  We have applied sql 2005 SP1 for maintenance plan improvements, but otherwise have made no changes.  

    So has SP1 changed the way memory is used? Will applying AWE allow SQL to use memory better?

  • Anonymous
    August 21, 2006
    Kevin, there was no significant changes with respect to memory management in SP1. In order for me to understand the problem you are facing: Could you please provide following info:
    A. Are you seeing actual performance degradation?
    B. Can you post/send me output for
    - dbcc memorystatus
    - sp_configure
    C. What are the numbers for memory usage and virtual memory counters in Task Manager for sqlservr.exe

  • Anonymous
    August 21, 2006
    Chris, are you still having this problem?
    If you are, could you please posts or send me output for:
    - BufferManager & BufferNodes perfcounters
    - dbcc memorystatus
    - sp_configure

    Thanks

  • Anonymous
    August 23, 2006
    Slavao,

    A. Are we seeing actual performance degradation?
    It has not been noticeably degrading (this is perceived as no trends have been plotted);  just the memory usage has changed.

    B. dbcc memorystatus ( not sure what you are looking for as there are a lot more but here are the top 3 in the list)
    Memory Manager:
    VM Reserved: 8344568KB
    Vm Committed: 1890276KB
    AWE Allocated: 0
    Reserved Memory: 1024KB
    Reserved Memory in Use: 0

    Memory node ID = 0
    VM Reserved: 6592KB
    Vm Committed: 1357696KB
    AWE Allocated: 0
    MultiPage Allocator: 6320
    SinglePage Allocator: 346488

    Memory node ID = 1
    VM Reserved: 8332344KB
    Vm Committed: 527032KB
    AWE Allocated: 0
    MultiPage Allocator: 42720
    SinglePage Allocator: 346488

    C. sqlservr.exe memory usage:
    mem usage: 1808536
    VM size: 2856148
    Note I did give the Sql account rights to “Lock Pages in Memory” which seem to have increased the amount of memory being used, but still not near what it was in the past.

    Thank for all your help and great information you provide, it is very much appreciated.

    Kind Regards,
    Kevin N.
    kevin@deepcovelabs.com

  • Anonymous
    August 23, 2006
    The comment has been removed

  • Anonymous
    August 24, 2006
    The comment has been removed

  • Anonymous
    August 26, 2006
    Stephen, I am a bit puzzled about your configuration: There is no SQL Server  2000 x64 version: Are you using SQL Server 2005 in x64 mode or you are running in SQL Server 2000 in WOW mode?

  • Anonymous
    August 27, 2006
    I guess maybe I'm a bit confused! %)  I'm running two instances of SQL Server 2000 Enterprise Edition (64bit) on a server with 8 Itanium2 processors.  This is my first time managing 64bit servers.

  • Anonymous
    September 28, 2006
    Yes having 1GB for OS could be a concern in such configuration. I would at least try to keep a number of available bytes for OS at around 2GB

    Having said that  it seems that your configuration will benefit from enabling locked pages in memory.

  • Anonymous
    November 13, 2006
    I thought I've seen reference recently to problems with SQL2005 not releasing memory when under pressure, and this was possibly due to the Lock Pages In Memory option. We have a server where this seems to be happening, i.e. SQL2005 uses all available memory and the server basically dies because there's no memory left for other processes. How do I make it work like SQL2000 where it will release memory when it detects memory pressure from other applications?

  • Anonymous
    November 13, 2006
    Mike, here is the answer to your question http://blogs.msdn.com/slavao/archive/2006/11/13/q-a-does-sql-server-always-respond-to-memory-pressure.aspx, please let me know if you have more questions

  • Anonymous
    February 14, 2007
    slavao,  would you mind looking at my post or I can copy it here.  I'm having problem getting sql2005 to use more than 13 MB. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1229631&SiteID=1&mode=1

  • Anonymous
    January 23, 2008
    PingBack from http://soci.hu/blog/index.php/2008/01/23/lock-pages-in-memory-64-bites-vason-sok-memoriaval-tessek-bekapcsolni/

  • Anonymous
    March 12, 2008
    Welcome to the Dynamics Ax Performance Team's blog. We're putting together a team introduction and hope

  • Anonymous
    July 23, 2008
    PingBack from http://coolhake.wordpress.com/2008/07/24/ax-database-configuration-checklist-part-1/

  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2225277-awe-enabled-on-64-bit

  • 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
    April 26, 2009
    Until now "Lock Pages In Memory" was an option only available to SQL Server Enterprise Edition customers.

  • Anonymous
    April 26, 2009
    PingBack from http://asp-net-hosting.simplynetdev.com/lock-pages-in-memory-in-sql-server-standard-edition/

  • Anonymous
    May 06, 2009
    PingBack from http://www.glorf.it/blog/2009/05/06/sql-server/lock-pages-in-memory-als-feature-der-standard-edition

  • Anonymous
    June 16, 2009
    Assumptions : Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL

  • Anonymous
    September 10, 2009
    So...is this still beneficial on SQL 2008? I only see references to SQL 2005.

  • Anonymous
    August 04, 2010
    All we e have have a shared db server with multipal sql instances. The server has 128Gb of ram and is 99% used between all the instances. I would like to know if locking pages in memory would help performance with this configuration and would sql "share" the memory with the other instances if one or the other need additional ram like it does now without the lock pages in memory configured. Windows 2k8 R2 EE sql server 2k5 SE CU 5 which I beleive addresses the standard edition supporting the lock pages in memory issues in previous versions.