Lock pages in memory and Virtual SQL Server

Question

VmWare 2008R2 Enterprise SQL server with Lock Pages in Memory ENABLED.... I have read the Jonathan Kehayias blog post referenced from Brent Ozar's blog via sp_Blitz and still not sure if I should have LPIM enabled. I have no ballooning on virtual server in the past month, max memory in set to 52428 MB out of 64GB, min memory is set to 1024, vCPU 8, host CPU% avg for month 24, vMachine CPU% avg for month 17. Currently ~52GB of pages locked in memory...I should note that 3rd party app has lots of ad hoc queries and lots of heaps and I average ~15K fowarded fetches/sec via sp_BlitzFirst. I rebuild the heaps weekly. FINALLY.. LPIM helping or hurting? What else can I look at?

• Erik Reasonable Rates Darling: Let's take a step back: What are wait stats for the server, and what are users complaining about? Start here: Getting Help with a Slow Server.

Answer 1

From your description it seems like things are fine for you even when LPIM privilege is their for SQL Server service account. If things are fine why do you want to change it I suggest you keep it like this.

Now LPIM protects paging of SQL Server process, so unless you have mis-configured VM host or some other applications running on OS which can leak or "Pull" memory from OS LPIM is actually not required. I as well believe that normally on VMware SQL Server should not be given LPIM unless required and forced by Ballooning or other application pulling memory unnecessarily. VMware ballooning is feature which should not be disabled but the VM host should be configured such that memory is balanced most of the time.

Quoting From Vmware Online Doc

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.

Moral: If you are not seeing any anomaly after enabling LPIM I suggest you leave it as it is but before doing this speak to your VMware admin and make sure the VMware configuration is correct