SQL Server: when to enable lock pages in memory (LPIM)

By Luke


SQL Server listens to the Windows Server operating system for memory notifications to trim its working set. Memory trimming is performed by the memory manager which has its own set of rules for how and when to trim the working set.

If the current available resources are insufficient to satisfy the request the memory manager will trim the working set. Small but repeated trimming of the working set could eventually result in significant portion of SQL Server being paged out to disk. This can result in performance issues and you will see the following message in the SQL Server Error Log:

“A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.”

This is where the minimum memory configuration setting comes in to play (sp_configure ‘min server memory‘). Windows Server will attempt to trim SQL Server down to its minimum working set (min server memory) and thereafter will page out to disk.

It’s important to configure both Min and Max memory correctly within SQL Server before enabling LPIM. Setting aside enough memory for the operating system and other running processes like Antivirus, Server Monitoring Software….and any other instances of SQL Server.

There is an old debate among DBA’s whether LPIM should be enabled by default, and I would say in earlier versions of Windows Server (2000,2003 etc.) that’s true. However, Windows Server 2008 (and onward) improves the contiguous memory allocation mechanism. This improvement lets Windows Server 2008 reduce the side effects of paging out the working set of applications when new memory requests arrive. Hard Trims can still happen, and you should be monitoring your SQL Server Error log for Error 17890 or the query text above.

With virtual machines, overcommitting memory is relatively common. The hypervisor will try to balance RAM allocations using memory ballooning. Whilst we recommend to avoid using memory ballooning around SQL Server we know it’s not always possible. Therefore In the event of memory ballooning you may want to enable LIPM to avoid unwanted trimming.

Microsoft explains how to enable this here

This Windows policy determines which accounts can use a process to keep data in physical memory, preventing the system from paging the data to virtual memory on disk
Locking pages in memory may boost performance when paging memory to disk is expected.

To enable the lock pages in memory option:

  • On the Start menu, click Run. In the Open box, type gpedit.msc.
  • On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
  • Expand Security Settings, and then expand Local Policies.
  • Select the User Rights Assignment folder. The policies will be displayed in the details pane.
  • In the pane, double-click Lock pages in memory.
  • In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
  • In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
  • Restart the SQL Server service for this setting to take effect.

 

Starting with SQL Server 2012 (11.x), trace flag 845 is not needed for Standard Edition to use Locked Pages.

<< Back to Knowledge Centre

Related Technologies:


Join Our Newsletter

Contact Email  *
First Name 
Last Name 
*Required Fields
I agree to the  Privacy Policy and  Terms of Use 

Here's what other people think

Google Rating
5.0
High standard, professional service. I've worked with Welldata for a number of years and always found their DBA's to be knowledgeable and proactive. Throw any question at them and it will be resolved, it gives real peace of mind knowing you have a partner you can rely on from emergencies to general questions. They also like a challenge and we do throw a few curved balls at them.read more
Paul Miller
Paul Miller
12:34 27 Aug 21
The support I receive from Well Data is extremely responsive. Richard Parsons is my contact and keeps me informed daily on the health of our SQL Databases. Whenever I ask for assistance from Well Data it is quick and professional.read more
Kay Riley
Kay Riley
13:41 19 Aug 21
Really know their stuff and their response times are very good. They provide us with a vital service.
Andy Cole
Andy Cole
13:36 19 Aug 21
The guys at WellData really know their stuff. We wanted a quick route to doing something complex and they sorted it for us very quickly and to a high standard. Very professional, no ego, just excellent and to the point consultancy.read more
Steve Goacher
Steve Goacher
11:31 14 Nov 19
I have worked with the WellData team for many years now and they have always delivered.I would highly recommend them to any business looking for best-value outsourced Expert DBA’s.Being able to offer a rolling contract on the basis that if they don’t deliver you can leave shows their confidence and commitment.Their professionalism and knowledge is second to none.read more
Petro Bartoszyk
Petro Bartoszyk
08:51 18 Oct 19
A very dedicated team of extremely professional and knowledgeable experts
Giulia Iannucci
Giulia Iannucci
14:08 16 Nov 18
We use Welldata for SQL support and are very happy with their service.
David Sadler
David Sadler
09:40 15 Nov 18
Really know their stuff and their response times are very good.
Andy Cole
Andy Cole
11:45 06 Nov 18
Our partnership with WellData has enabled us to resolve critical database problems. Their attitude and service makes them a natural extension to our own technology team, which extends our capability in a critical area of our business.read more
Steve Fenton
Steve Fenton
10:56 05 Nov 18
Having worked with the team at Welldata for approaching 17 years, my company and I have always found them professional, reliable and generally a great firm to work with.read more
John Lynes
John Lynes
14:00 04 Oct 17
WellData provide an excellent level of support and their team are very knowledgeable and always go the extra mile. I would wholeheartedly recommend them.read more
Stefan Parrott
Stefan Parrott
13:12 19 Sep 17