When SQL Server reserves new space on a disk, it initializes the space with zeros. This behavior can be switched off thereby reducingthe execution time of some operations and the load on the disk subsystem.
Disk space reservation without initialization is called Instant File Initialization. This feature is not widely known, although its use has become possible starting with SQL Server 2005.
The benefits of the Instant File Initialization are the following:
1. Accelerating the creation of a new database.
2. Reducing delays and time required to expand data files.
3. Reducing the start time of SQL Server due to faster tempdb initialization.
4. Reducing time for restoring from a backup, because SQL Server reserves space for files before restoration and then transfers the information from the backup into the files.
It is important to note that Instant File Initialization works only for data files (MDF and NDF). Log files (LDF) are always zero-initialized.
How to use Instant File Initialization?
- The option may be enabled fairly simply. Open SQL Server Configuration Manager to see the name of SQL Server instance.
- After that, in the Local Security Policy window, navigate to User Rights Assignment and select Perform volume maintenance tasks.
- On the Local Security Setting tab, add the server instance
- SQL Server instance verifies the rights required for working with Instant File Initialization only once – during the launch. That is why you need to restart SQL Server to apply the configuration.
When you restore for the first time to a server or you use WITH MOVE, the operating system has to create new files for the database. Starting with SQL Server 2005, there’s support to work with the operating system (which has to be XP or better or Windows Server 2003 or better) to instantly initialize the files. To make this work, you have to make sure you have the appropriate security settings for the account that SQL Server is running under. You have to include that account in the Windows Administrator group on the server, or you have to add the account to the Perform Volume Maintenance Tasks security policy, or you have to just give it the SE_MANAGE_VOLUME_NAME special privilege. Once done, there’s nothing else you have to do. You’ll get instant file initialization which can result in astronomical time savings.
You can also enable this feature at the time of installing SQL Server on the server.
Sources:
Comments