Should we configure Lock Pages in Memory(LPIM) for SQL Server 2016/2017/2019 on Windows 2019

Question

We are configuring new SQL Servers standalone and enterprise version , Alwasyson systems . Question is do we have to enable Lock Pages in Memory option in windows edgroup.msc part of best practice which will reflect from SQl Server service in Configuration manager for boost performance of the SQL Servers . any advice please
https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-ver16
https://www.mssqltips.com/sqlservertip/6918/lock-pages-in-memory-sql-server/#:~:text=Lock%20Pages%20in%20Memory%20(LPIM,very%20helpful%20for%20SQL%20Server.

SELECT a.memory_node_id, node_state_desc, a.locked_page_allocations_kb
FROM sys.dm_os_memory_nodes a
INNER JOIN sys.dm_os_nodes b ON a.memory_node_id = b.memory_node_id
go
SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Answer 1

Question is do we have to enable Lock Pages in Memory option in windows edgroup.msc part of best practice which will reflect from SQl Server service in Configuration manager for boost performance.

Lock Pages in Memory (LPIM) is not about performance boosting. It is about stability. Let's say that something else on the server starts to grab memory. It could be a leaky driver, it could be you that start copying a large file or it could be anything else.

Because the operating system sees that there is a shortage or memory, it may send processes to the swap file. And of course, since SQL Server takes up lot of memory, Windows will think that's a great candidate. However, the reason that SQL Server has a lot of memory is that it has read data from disk to RAM as a cache. But having the cache on the page file is quite pointless, so SQL Server could just as well trim the cache itself. And eventually it will, since it knows that there is memory pressure.

The LPIM permission prevents SQL Server from being sent to the cache file. It will still trim its memory, but the disaster of having data in the page file for slow access cannot occur.

LPIM is generally a good thing for SQL Server, but you can imagine what would happen if all processes on the machine has LPIM. Poor Windows would not be able to send anything to the page file and have no choice to explode. To the menace for everyone one.

If you have a server which is dedicated to a single instance of SQL Server, LPIM is a good idea. If the server has several instances of SQL Server, or also a web server, Reporting Services or whatnots, you should think twice before you grant LPIM to the service account for SQL Server.

Answer 2

Hi @Ashwan ,

Maybe NO

Lock pages is a windows account based policy, it is not somrthing you really set indside SQL Server, even you should be careful in virtualized environment to use it, as long as you give SQL Server enough memory, you do not have to worry about paging it

You can look this article, it can give you more help

https://www.brentozar.com/training/fundamentals-database-administration/lock-pages-memory-lpim-6m/

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

If your server is dedicated to SQL Server, then you should set LPIM. If it is not, you will need to decide if SQL Server service is the most important service on the server.

As Erland said, this prevents Windows from swapping out critical parts of SQL Server to disk cache and will improve stability and overall performance.