Check instant file initialization and lock pages in memory options

Study your server reports do the work for you!

Instant file initialization and lock pages in memory are 2 windows operating system options that can help us improve our overall SQL Server performance.
These options are already checked in the Server Dashboard report available in my free tools.

We will talk about both options and how to check them.

Note that these options are for SQL Server running under Windows OS, for Linux systems you can ask Carlos Robles via his webpage or twitter. He has a lot of experience working with databases (SQL Server, Oracle, MySQL, Sybase) running under UNIX environments.

Instant file Initialization

When you create any file on the operating system, any data left in disk is overwritten by filling with zeros all the file, that could take some time depending of the file size.
For SQL Server this is done when you restore a backup, perform an auto growth operation, create a data/log file (if you put an initial value of several GB this zeroing could take a long time to complete), so this can impact our SQL Server performance.
To avoid that, the zeroing operation can be skipped leaving the "dirty" data on the disk, improving the speed of file operations.

To check if instant file initialization is enabled

Just check the error log and find for the "instant file initialization" entry

exec master..xp_readerrorlog 0,1, "Instant file initialization"

This will tell you if it is enabled or disabled for your system

To enable Instant File Initialization

Starting SQL Server 2016, this option can be configured at setup by checking the "Grant Perform Volume maintenance task privilege to SQL Server Database Engine Service" option

If the option was not enabled during setup, or for older versions, you have to add SQL Server service account to the Perform volume maintenance tasks local policy on your system.
You can check how to do it here.

Lock Pages in Memory

This option allow any given OS process to keep data in the memory, instead of paging it to disk (virtual memory).
Enabling this option we can improve performance by making SQL Server process read pages from physical memory (of course if the page is available in memory).

To check if Lock Pages in Memory is enabled

Run the following query:

/*Lock pages in memory*/
FROM sys.dm_os_memory_nodes MN
INNER JOIN sys.dm_os_nodes N 
ON MN.memory_node_id = N.memory_node_id
WHERE N.node_state_desc <> 'ONLINE DAC'

If lock pages in memory is enabled, the value will be greater than 0.

To enable Lock Pages in Memory

You have to add the sql server service account to the Lock pages in memory policy.
You can check how to do it here.

Also remember, these options are already checked in the Server Dashboard report available in my free tools.





Popular posts from this blog

Checking Azure SQL DB service tier via T-SQL

Install Python on Windows

Quick tip: Zoom in Azure Data Studio