Introduction
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.
BTW, if you want to help improve SQL Server 2022, please take a moment to read and hopefully upvote my suggestion regarding my SQL Server 2022 DMV Wish List.
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.
Final Words
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!
If a later change is made to running under a different service account – will the LPIM and IFI settings apply to the new account or will they need to be enabled again? Assuming that the service account is changed via configuration manager.
I have not actually tested that, but my hope would be that if you used SQL Server Configuration Manager to enable LPIM and IFI, it would carry over if you changed the service account in SQL Server Configuration Manager
SQL Server 2019 FCI running on Server 2019. On active node IFI indicates no via advanced tab of SQL Server Configuration manager but SQL Server errorlog, sys.dm_server_services, and GP all indicate that it is on. We are using gMSA for service account if that matters. Your thoughts please.
Hi Joe,
My guess is that the gMSA might be the issue. Those are not as commonly used as regular domain accounts, and perhaps the new code in SQL Server 2019 Configuration Manager does not handle them correctly. I would file a bug with Azure Feedback.
I would believe what the various DMVs and the SQL Server Error log say about LPIM and IFI.
Thank you for your article, great that Joe mentioned gMSA as this is the default for our SQL service accounts.
How would one benefit from this change running Microsoft SQL Server on Windows Server Core? Can this pane be accessed remotely?
If you are running on Windows Server Core, you probably should be using PowerShell to change these settings. You can use this: https://docs.dbatools.io/Set-DbaPrivilege
Great 5 star article Glenn. Thank you!
Very interesting, much easier to change and inspect. Thanks a lot Glenn.
Yes, it is easier to use.