Microsoft launched SQL Server 2019 on November 4th 2019, so it has been GA for a little over two years now. The latest Cumulative Update (CU) right now is SQL Server 2019 CU14. This post describes some Hidden Improvements in SQL Server 2019, and why they make it easier to configure and manage SQL Server.
Hidden Improvements in SQL Server 2019
One improvement in SQL Server 2019 that I was not aware of until quite recently is in SQL Server Configuration Manager. With SQL Server 2019, you can use SQL Server Configuration Manager to enable or disable several additional settings in SQL Server. These include:
- Customer Feedback Reporting
- Error Reporting
- Instant File Initialization
- Lock Pages in Memory
The most interesting two are Instant File Initialization (IFI) and Lock Pages in Memory (LPIM).
If you go to the Properties window for the SQL Server Service in SQL Server Configuration Manager, and then click on the Advanced tab, you will be able to see these settings.
Slightly below the IFI setting is the Lock Pages in Memory (LPIM) setting. Each of these settings has a dropdown menu that let you select Yes or No to enable or disable the setting.
Using Local Group Policy Editor
With SQL Server 2014 and older, the main way to enable/disable IFI or LPIM is to use Local Group Policy Editor. With older versions of SQL Server, it was a typical post-installation configuration task.
Instant File Initialization (IFI)
Starting with SQL Server 2016, you could enable IFI by checking a checkbox in the SQL Server installation program. This had the effect of granting the “Perform volume maintenance tasks” right to the SQL Server Service account. But what if you forgot to do that?
With SQL Server 2017 and older, you still had to run Local Group Policy Editor (GPEDIT.MSC), and navigate down to User Rights Assignment and grant the “Perform volume maintenance tasks” right to the SQL Server Service account. You also had to know what account was the SQL Server Service account.
Lock Pages in Memory (LPIM)
With SQL Server 2017 and older, you still had to run Local Group Policy Editor (GPEDIT.MSC), and navigate down to User Rights Assignment and grant the “Lock pages in memory” right to the SQL Server Service account. You also had to know what account was the SQL Server Service account.
There is no option to enable LPIM in the SQL Server 2019 setup program. I agree with Erik Darling that this option should be in the SQL Server 2022 setup program.
Using GPEDIT.MSC is pretty cumbersome, but I have done it so many times, I could probably do it in my sleep! BTW, my standard practice and recommendation is to enable both IFI and LPIM on nearly all SQL Server instances.
There is a very slight security risk associated with IFI, but your organization would have to be doing multiple bad things for it to be an actual risk. LPIM could be problematic if you don’t set max server memory to an appropriate value to ensure that the operating system is never too low on memory. But that is something you should already be doing, even if LPIM is not enabled.
Changing the IFI and LPIM settings in SQL Server Configuration Manager is quicker and easier than using Local Group Policy Editor. Plus, you don’t even have to know what account is the SQL Server Service account. This makes it much easier for a non-DBA to change the setting, which could be good or bad…
Just to be clear, you still have to restart the SQL Server Service for these changes to go into effect, just like before.
If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!