Sunday, May 28, 2017
 
 
     

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).

Background:

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.

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 an 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 Zero’s on every disk block before using the disk space for above listed operations. This can save tremendous amount of time and improve the performance of 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 a 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 zero’s 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 grant SQL Server service account to PERFORM VOLUME MAINTENANCE TASK local security policy. Any user of the local administrators 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 it will launch local security policy window.

Step2: From Local Security Policy, Expand Local Policies

Step3: And then click on “User Rights Assignment”

Step4: From the right pane, double click “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 which 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.

Thank you,

Saleem Hakani

Improve File Operations
Instant File Initialization
Perform Volume Maintenance Tasks
SE_MANAGE_VOLUME_NAME
SQL-WIFI
WIFI
Windows Instant File Initialization
Minimize
SQL Article Tags
HOME   |   SQL ARTICLES   |   SCRIPTS   |   DISCUSSIONS   |   SQL TEAMS   |   JOBS   |   ABOUT US
Copyright [2014] by SQLCOMMUNITY.COM