Why do I need to use instant file initialization?
In many cases, writing zeros across the disk space before using that space is unnecessary. Instant file initialization (IFI) allows SQL Server to skip the zero-writing step and begin using the allocated space immediately for data files. It doesn’t impact growths of your transaction log files, those still need all the zeroes.
How does the database file initialization process work?
The Database File initialization process writes zeros to the new regions of the file under initialization. The duration of this process depends on size of file portion that is initialized and on the response time and capacity of the storage system.
How to view InnoDB initialization information during startup?
To view InnoDB initialization information during startup, start mysqld from a command prompt, which prints initialization information to the console. For example, on Windows, if mysqld is located in C:\\Program Files\\MySQL\\MySQL Server 8.0\\bin, start the MySQL server like this:
When to use autoextend and Max in InnoDB?
The autoextend and max attributes can be used only for the data file that is specified last. When the autoextend attribute is specified, the data file automatically increases in size by 64MB increments as space is required. The innodb_autoextend_increment variable controls the increment size.
How long does it take to restore database without instant file initialization?
Database restoration without instant file initialization took approx. 26 seconds Database restoration with instant file initialization took approx. 21 seconds You might see it is a minimal improvement but think in terms of an extensive database. You might have a database in TB’s, and restoration takes a very long time.
How long does it take to initialize a file with IFI?
The larger the growth operation, the more noticeable the performance improvement is with IFI enabled. For instance, a data file growing by 20 GB can take minutes to initialize without IFI. Read more about waits for file growths here.
How does instant file initialization work in TDE?
Instant file initialization does not work with the Transparent Data Encryption enabled database. Once we configure TDE and try to expand data file, it uses the zeroing process Instant file initialization does not have any impact on log files. Log files are always zero-initialized