 |
| 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*/
SELECT
MN.locked_page_allocations_kb
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.
Sources:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-the-lock-pages-in-memory-option-windows?view=sql-server-2017
