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.