Ever wondered why SQL Server takes long time to perform below operations?
Create a database
Expand data files for additional growth
Automatic data file growth due to auto-grow settings
Restoring a database, etc.
Today we will be looking in to a powerful and yet not very popular feature that many DBA’s can benefit from called SQL-WIFI (Windows Instant File Initialization).
Every time SQL Server needs space for data or log file for any activities listed above, it will grab the required space on the disk and initialize every block of that disk with Zero’s before it can use that space for data or log files. It does that to overwrite any existing data left on the disk from previously deleted files.
Imagine if you were to create a 100GB database or a restore a 500 GB database with the default configuration of SQL Server, it will first call the API’s that will overwrite every block on the disk where the file will be restored with 0 before it starts creating or restoring the database on that disk. This can take a long time depending upon how powerful your server is or the resources available on the server.