Script to verify Lock Pages in Memory and Instant File Initialization.

  

There are several situations where we need to verify if Lock Pages in Memory and Instant File Initialization are enabled or not. Like while setting up new servers, sometimes during performance tuning and sometimes company policy verification and so on.

The general method is to go to run and dig deep in secpol.msc for Instant File Initialization and gpedit.msc for verifying Lock Pages in Memory.

Below SQL queries help in verifying Lock Pages in Memory and Instant File Initialization are enabled or not directly from SSMS:

 

-- Query to check Lock Pages In Memory

-- If sql_memory_model_desc column output is LOCK_PAGES then it means Lock Pages in Memory is enabled.

Select sqlserver_start_time, sql_memory_model_desc from sys.dm_os_sys_info

Go

-- Query to check Instant File Initialization

-- If instant_file_initialization_enabled column output is Y, it means  Instant File Initialization is enabled for that particular service.

 Select servicename,instant_file_initialization_enabled from sys.dm_server_services

Sample Output:

 

 

Note: These DMVs works from SQL Server 2012 SP4 & above versions.

 

Let me know in the comments section below if any other easier way to get these details, it will help me and readers.

 

Thanks VV!!

#LockPagesInMemory, #InstantFileInitialization #MSSQL #sql #sqlserver #script #sqlblog