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 http://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 (24)

  1. bill salkin says:

    Hello Slava,

    On a box with 16GB of RAM, Windows 2003 Server Standard x64 and sql2005 Standard x64 I’m told that the OS will claim 8GB of this memory leaving a max for SQL of 8GB. Is this true? If so, is there a way we can move the "dividing" line so sql gets 12GB and the OS 4GB??

  2. slavao says:

    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

  3. Chris F says:

    We have a Box with 2003 x64 Enterprise and SQL 2005 x64 standard installed with 8GB of RAM. we dont ever go above 3.7 GB of RAM used by SQL server. am I missing a setting to tell SQL to take as much RAM as it needs? For as busy as we are i would assume the Memory used to be much higher.

  4. Kevin N. says:

    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?

  5. slavao says:

    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

  6. slavao says:

    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

  7. Kevin N says:

    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

  8. slavao says:

    In general if SQL Server allocates memory on demand so if you don’t see performance degradation it shouldn’t be a problem. Keep in mind that even when you set max server memory = min server memory SQL Server 2005 won’t allocate it right a way – only on demand.  

    Having said this, I have couple of observations here:

    A. Locked pages in memory setting didn’t take effect, If it did you would see AWE counters not 0. Make sure that you restart the machine after enabling lock pages in memory privilege.

    B. You either have max server memory setting down to 3GB, OS pages SQL Sever out, or server doesn’t need memory. If it is former reset max server memory to either default or 6GB.  If it is paging then you need to find out reason for OS paging SQL Server out. Most likely it is due to other applications sharing box with SQL Server. You can put pressure on other applications by configuring min server memory to high value. Make sure that min is always <= max though.

    Good luck!

  9. Stephen R says:

    On a box with 32GB of RAM, Windows 2003 Server Enterprise x64 and two instances of SQL Server 2000 Enterprise x64 my current Total Server Memory for each instance is 26GB and 4GB with OS Available Bytes at 1GB.  Should I also turn on Lock pages in memory? Is the 1GB Available Bytes a concern?  We are currently experiencing a automatic restarting of the 4GB instance with an error "The server’s call to a system service failed unexpectedly".

  10. slavao says:

    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?

  11. Stephen R says:

    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.

  12. slavao says:

    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.

  13. Mike K says:

    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?

  14. slavao says:

    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

  15. st8floorsup says:

    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

  16. Welcome to the Dynamics Ax Performance Team’s blog. We’re putting together a team introduction and hope

  17. Until now "Lock Pages In Memory" was an option only available to SQL Server Enterprise Edition customers.

  18. Assumptions : Dedicated SQL Server 2005 Server (does not run any other major applications besides SQL