Saturday, January 20, 2018
Term: WIFI
1 post(s)

SQL Server - File Operations on Steroid

By Saleem Hakani on 9/25/2014

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.

Improve File Operations
Instant File Initialization
Perform Volume Maintenance Tasks
Windows Instant File Initialization
SQL Article Tags
Copyright [2014] by SQLCOMMUNITY.COM