Home > Blogs > Virtualize Business Critical Applications


When to use Lock Pages in Memory with SQL Server

Granting the Lock Pages in Memory user right to the SQL Server service account prevents SQL Server buffer pool pages from paging out by Windows. This setting is useful and has a positive performance impact because it prevents Windows from paging a significant amount of buffer pool memory out of the process, which enables SQL Server to manage the reduction of its own working set.
 
Setting the Lock Pages in Memory user right is a good performance optimization practice when Tier 1 mission-critical SQL Server. When setting the SQL Server Lock Pages in Memory user right, the virtual machine’s memory reservation should also be set to match the amount of the provision memory. Setting virtual machine memory reservations prevent the balloon driver from inflating into the SQL Server virtual machine’s memory space. Lock Pages in Memory should also be used in conjunction with the Max Server Memory setting to avoid SQL Server taking over all memory on the virtual machine.
 
For instructions on enabling Lock Pages in Memory, refer to Enable the Lock Pages in Memory Option (Windows) (http://msdn.microsoft.com/en-us/library/ms190730.aspx).
 
While Lock Pages in Memory can optimize performance, it may not be optimal for all kinds of SQL Server deployments on vSphere. When Lock Pages in Memory is used, because SQL Server memory is locked and cannot be paged out by Windows, you might experience negative impacts if the vSphere balloon driver is trying to reclaim memory from the virtual machine. For lower-tiered SQL Server workloads where performance is less critical, the ability to overcommit to maximize usage of the available host memory might be more important. When deploying lower-tiered SQL Server workloads, VMware recommends that you do not enable the Lock Pages in Memory user right for lower-tiered SQL Server workloads. For lower tier SQL workloads, it is better to have balloon driver manage the memory dynamically. Having balloon driver dynamically manage vSphere memory can help maximize memory usage and increase consolidation ratio.

2 thoughts on “When to use Lock Pages in Memory with SQL Server

  1. http://relinkto.net

    Outside your home or office, you maay also need to call for a professional locksmith to unlock your
    car door the right way during those unfortunate times when you either lose yur automotive keys or leave them in the vehicle by
    chance. I know in her head she was saying, Whaddya mean
    I am supposed to believe this story. A locksmith however does more than just make and fits locks and keys.

    My page: Clic Para Continuar, http://relinkto.net,

    Reply
  2. Cornell

    Also check that you don’t delete emails in your email client once it logs
    into the server. User whom already owns an account with other
    third party Captcha services will find that
    GSA Integrates with most of the popular captcha solving
    services including, decaptcher, deathbycaptcha, bypasscaptcha, image
    typez, captcha sniper & captcha infinity ,and more ‘.

    Before submission the content is encoded to a format which works for the site.

    My blog post GSA SER; Cornell,

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>