With the release of SQL Server 2019, there were enhancements that are known and talked about. There are few minor changes in SQL Server Configuration Manager which were un-noticed. In this blog, I would talk about enhancement in SQL Server 2019 Configuration Manager. Let us learn how to enable lock paged in memory (LPIM).
Earlier I wrote a blog about Instant file Initialization option in SQL Server 2019 Configuration Manager. SQL SERVER 2019 – How to Turn On or Enable Instant File Initialization?
In this blog, I would share another new setting visible, “Lock Pages In Memory”. Most of the DBAs know about this and know how to make changes. I have written the below blog which covers the same.
Enable Lock Pages in Memory
When OS is running low on the memory, it starts clearing the cache of various application which is installed on the server. During the process, Windows also clears the plan cache for SQL Server and releases the memory back to the OS. However, there are some cases, the memory is released back to OS even though there is no memory pressure. In such a scenario we should Enable Lock Pages in Memory (LPIM).
Let’s look at the SQL Server 2019 Configuration Manager. You need to go to the “Advanced” tab to view the setting.
This is the dropdown from where you can view or change the settings. As the description says “Grants Lock Pages In Memory privilege to SQL Server Database Engine Service Account”
Once I change the settings, I verified and it works as documented, the account got added automatically without all the hassle.
I hope you enjoyed reading as much as I enjoyed writing it. I will share more new things about SQL Server 2019 in upcoming blogs.
Reference: Pinal Dave (https://blog.sqlauthority.com)
