Developers Choice: Programmatically identify LPIM and IFI privileges in SQL Server

Lock Pages in Memory and Instant File Initialization privileges are couple of configuration settings which every DBA, SQL Server consultant or enthusiast have it in their checklist to ensure they see a predictable performance for their SQL Server instance. While Lock Pages in Memory privilege information is logged in SQL Error log, Instant File initialization information was first introduced in SQL Errorlog starting SQL Server 2016 RTM and later added to SQL Server 2014 with SP2.

When you are managing, administering or monitoring large deployment of SQL Servers, it is still cumbersome to programmatically query SQL Error log to check if these permissions are enabled for the SQL Server service account. With SQL Server 2016 SP1 and SQL Server 2012 SP4, we have added new columns in the DMV which now makes it easy to develop scripts to programmatically query and report whether Lock Pages in Memory and instant file initialization privileges are enabled on a given instance of SQL Server.

Lock Pages in Memory

With SQL Server 2016 SP1 and SQL Server 2012 SP4, there are two new columns viz sql_memory_model and sql_memory_model_desc added to sys.dm_os_sys_info which can be used to identify if Lock Pages in Memory (LPIM) permissions are assigned to SQL Server service account.

One important thing to be aware of is, the new columns report the sql memory model in use since the startup of SQL Server instance which is the information desired. There are no checks performed at the OS level for LPIM permissions for the sql service account. If during the SQL Server startup, LPIM privilege is present in SQL server service account process token, SQL Server uses locked pages (non-pageable) to allocate sql memory. Further, if you are running Enterprise edition of SQL Server with LPIM privileges assigned to sql service account and trace flag 834 turned ON, SQL Server uses large pages to allocate sql memory.

To check if the Lock Pages in memory privilege is in effect for a given SQL Server instance, you can query sql_memory_model in sys.dm_os_sys_info and look for values greater than 1.

If LPIM permissions is missing in service account process token, conventional memory model is used and the same is reported by DMV (sql_memory_model = 1). If now, lock pages in memory privilege is assigned to SQL server but SQL service is not restarted, DMV will continue to report conventional memory model since that is the memory model in effect since startup. After restart, SQL Server uses locked pages in memory model and the same is reported by the sql_memory_model and sql_memory_model_desc in sys.dm_os_sys_info.

Instant file initialization

With SQL Server 2016 SP1 and SQL Server 2012 SP4, there is new column instant_file_initialization_enabled added to sys.dm_server_services which can be used to identify if instant file initialization is enabled.

Like sql_memory_model, there are no checks performed at the OS level for SeManageVolumePrivilege for sql service account. If during the startup of SQL Server, SeManageVolumePrivilege is present in the sql service process token, instant file initialization is enabled and is in effect until the sql service is restarted. The column instant_file_initialization_enabled reports whether IFI is enabled and in effect since the startup of SQL Server instance. If at the OS level SeManageVolumePrivilege is revoked but sql server is not restarted, instant file initialization will still be in effect and the same is reported by instant_file_initialization_enabled .

Since there are no checks performed at the OS level for the SeManageVolumePrivilege, the column instant_file_initialization_enabled is only applicable to SQL Server Database engine service account in sys.dm_server_services and is null otherwise.

To check if instant file initialization is enabled in SQL Server, you can query sys.dm_server_services where instant_file_initialization_enabled is not null and look for values report by instant_file_initialization_enabledas shown below.

SELECT servicename,instant_file_initialization_enabled as [ifi_enabled] from sys.dm_server_services where instant_file_initialization_enabled is not null

So make sure to update your maintenance scripts, health check or monitoring solution to add these checks for SQL Server for SQL Server 2016 SP1 and SQL Server 2012 SP4. If you are checking for Lock Pages in memory, it is also important to ensure and check if max server memory is appropriately set on the server.

We have updated our BPCheck and SQL Performance Baseline tools in Tigertoolbox github repository to include these checks. If you are using SQL Performance Baseline solution, please ensure to download and re-deploy the new reports from github repository. Only reports needs to be re-deployed in SSRS, there are no scripts required to be run if you have already ran the scripts while configuring it the first time. The new reports will show if the Lock Pages in Memory and IFI is enabled for instances which are on or above SQL Server 2016 SP1 as shown below.

 

 

Parikshit Savjani
Senior Program Manager (@talktosavjani)