page contents

About the Post

Author Information

How does Instant File Initialization Help Your Databases?

In an earlier article, I spoke about a Best Practice called File Initialization. I walk you through setting it up I pointed you to a blog by Kimberly Tripp. Today, I want to show you how much time it will save you.

I know this may seem like a silly question, but don’t you want to make your database server faster? More importantly, don’t you want to get back some time for yourself during the day? Let’s think about all the ways that you can do this. (Proper database design, proper indexes, designed hardware choice for your workload, etc.)

One method (that is often overlooked) will make sure that when your database needs to grow – i.e. a data file needs to expand, more files are added, or a database is restored – that this growth happens quickly.

This can be accomplished by adjusting a local policy at the Windows Server level called Perform volume maintenance tasks. In a nutshell, this will give your SQL Server service account rights to this policy allows and allow it to take advantage of instant file initialization (IFI). Normally, when you either grow a file or create one, the entire space to be used will be overwritten with zeros. This can take some time depending on the size of this file. With rights to use IFI, SQL Server skips that step – the space is claimed on disk and is overwritten as needed.

As you will see below, it makes database growth faster.

Let me show you how it makes database growth faster

I have a SQL Server instance that is using SDER2 as the service account. This account is not an administrator at any level. (Either local or domain administrator)

1-Services

Next, I open gpedit.msc via the run command:

2- Run

Navigate to Computer Configuration\Windows Settings\Local Policies\User Rights Assignment. Then look for Perform Volume Maintenance Tasks. As you can see, by default, only Administrators have permission to Perform volume maintenance tasks.


Now I’m going to create a 1 GB size database and see how long does it take to create this new file.


Statistics Time tells me that it took 4774 MS:


Now I go back to Computer Configuration\Windows Settings\Local Policies\User Rights Assignment\Perform Volume Maintenance.


Next I add the SQL (SDER2) account to the Perform volume maintenance tasks. Hit OK to save.

Last, you must restart the SQL Server service for this change to take effect.

Now I create a second 1 GB database to compare the results.


Notice the difference in the Statistics Time that shows you how much faster it took:

Keep in mind 4774 ms versus 1209 ms and it may not seem like a lot, but you need to remember that this was only 1 GB in size. A lot of databases today are somewhere between 10 GB to 1 TB, so when you start to add all of those extra ms to each additional GB it can start to add up. This effects all of you database backups, restores and new database creations. I am sure you are like me and every second counts in your day, since we rarely have enough time in the day for ourselves.

Tags: , , ,

Trackbacks/Pingbacks

  1. Configuring SQL Server 2012 Always On Lab- Part 4 - SQL Server - SQL Server - Toad World - May 28, 2014

    […] I setup user rights for the accounts to allow for Instant File Initialization (and I wrote another one here) by opening […]

Copy Protected by Chetan's WP-Copyprotect.