Enabling instant file initialization is another way to mitigate the adverse effects of file growth events. SQL Server's default behavior when creating or growing a file is to initialize the new file space on the disk with zeros. This is primarily a security feature to erase any information left by previously deleted files.

File initialization happens when creating a database, adding log or data files to an existing database, increasing the size of a file, auto-growth, and restoring a database or filegroup. Find out if instant file initialization is on by running xp_readerrorlog with a search parameter of Database Instant File Initialization, or by querying the instant file initialization enabled field of sys.dm_server_services.

Enabling this feature will significantly speed up disk space allocation, thereby improving the performance of auto-growth. Microsoft does recommend enabling this feature, saying that the benefit far outweighs any security risks. Instant File Initialization has to be enabled through local security policy by adding the SQL Service account to Perform volume maintenance tasks under local policies user rights assignment. After enabling instant file initialization, the queries reflect the new setting