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
