what is Instant File Initialization? how to enable it ?

what is Instant File Initialization? how to enable ?

Do you have instant file initialization turned on?  lets find its uses and how to enable ..for better performance ..

What is Instant File Initialization

It means when ever we create a new db  or grow a data file or when we restore a db, this option will be very helpful to allocate the required data pages at earliest. new pages allocated to the database first need to be filled with binary zeroes.  The process of writing binary zeroes takes time.  Therefore, when instant file initialization is not turned it takes SQL Server longer to create or expand a database.  The more space added to a database the longer it takes.

Why does SQL Server Not Perform Instant File Initialization by Default?

By writing binary zeroes to newly allocated disk space SQL Server makes sure pages allocated to the database don’t contain old disk images.  Without overwriting those data pages, a person might be able to peek into the internal structure of a data page and see those bits and bytes of old data stored on the disk.  By not zeroing out those data pages there is a security risk that old data can be viewed by outputting raw database pages.

How to Turn on Instant File Initialization

In order to turn on instant file initialization you need use the following steps:

1. Logon to your SQL Server with an account that is a member of the local Windows Administrator group. 
2. Open the Local Security Policy application by running secpol.msc from a command prompt.
3. In the left pane, click the “Local Policies”:
   
4. In the left pane double-click on the “User Rights Assignment”
   
5. Double-click on “Perform volume maintenance” item.
   
6. Click the “Add User or Group” button
   
7. Add the account name for which the SQL Server is running under in the dialog below:
   
8. Click the “Apply” button, and then close the “Local Security Policy” dialog box.