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 into a powerful and yet not very popular feature that many DBAs can benefit from called SQL-WIFI (Windows Instant File Initialization).
Background:
Every time SQL Server needs space for data or log files for any activities listed above, it will grab the required space on the disk and initialize every block of that disk with Zeros 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 restore a 500 GB database with the default configuration of SQL Server, it will first call the APIs 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.
Here’s an example of creating a 100 GB database called WIFI_TEST. As can be seen from the image below, it took 11 minutes and 43 seconds to create a 100 GB database.
WIFI (Windows Instant File Initialization)Â to the rescue:
Initializing blocks to zero every time on empty disk space is simply a waste of time. To prevent this, SQL Server allows you to initialize data files instantaneously. This means SQL Server will no longer need to write Zeros on every disk block before using the disk space for the above-listed operations. This can save a tremendous amount of time and improve the performance of the above-listed operations.
Before using WIFI (Creating a 100 GB database called WIFI_TEST)
After using WIFI (Creating a 100 GB database called WIFI_TEST)
As can be seen from both before and after images, a 100GB database took 11 minutes and 43 seconds and after enabling WIFI, it took only 14 seconds. This test was performed on an x64 i7 4470 CPU @ 3.4 GHz 8 Core Intel machine with 12 GB RAM. That’s a 99+% performance improvement.
Important: Only the data files can be initialized instantaneously and not the log files. Log files will always have to be zero-initialized every time before they are being used by SQL Server as SQL Server uses zeros to track the point at which crash recovery is marked as complete.
In order to bypass the zeroing process, you should enable WIFI for SQL Server.
How to Enable WIFI for SQL Server:
All you need is to grant a SQL Server service account to PERFORM VOLUME MAINTENANCE TASK local security policy. Any user of the local administrator’s group can do this.
Here are the steps you can perform:
Step1: From Widows Server, Launch Local Security Policy, You can also type SECPOL.MSC and will launch a local security policy window.
Step2: From Local Security Policy, Expand Local Policies
Step3: And then click on a User Rights Assignments
Step4: From the right pane, double clicks perform volume maintenance tasks and add the account you are using to run SQL Server service, and close the Window
Step5: Restart SQL Server Service
NOTE: WIFI is not available for databases that have TDE enabled.
Security Implication: There is however a security implication of Using Instant File Initialization. Without zeroing out the database files (I.e. by using WIFI), someone could copy the DB files (or take a backup of the database), and examine the data in the unformatted pages to read the data that had previously been on that region of the disks. In that way, they could gain access to data that they have no rights to, in the form of previously deleted files.
the best practice is to weigh the benefits and risks of Windows Instant File Initialization before it is implemented. However, if you are implementing it in a properly controlled environment, you’ll notice a huge performance increase for your database file operations
