Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Instant File Initialization Speeds SQL Server

Sometimes, its just the smallest of details that can make all the difference. For example, on my test system (see the end of this posting for a description), I created a new 50GB database. The database creation process took about 5 minutes and 50 seconds to complete.

Next, I populated the database with over 61 million rows of data, which virtually occupied all of the available space within the newly created database. After that, I backed up the database using SSMS, and then I deleted the original database.

At this point, I restored the database from the backup using SSMS. Below, you see the typical Restore Database screen.

image 

At the bottom, right-hand side of the screen in the Progress box, notice the “Executing (0%)” indicator. Between the time I clicked the OK button to begin the restore, and when the “Executing (0%)” counter began to move, it took about 5 minutes and 50 seconds. At that point, the counter began to increment and the database was restored.

Now I make one very small change to my SQL Server instance (I’ll describe it in just a moment), and then I repeat the above steps (after deleting the database I just restored). First, I created a new 50GB database. This time, instead of taking 5 minutes and 50 seconds to create the database, it takes just under 2 seconds, a savings of about 5 minutes and 48 seconds. Next, I populated the database with the same amount of data as before, backed it up, and then deleted the original file. When I restored the database this time around, instead of having to wait 5 minutes and 50 seconds before the backup began to restore, I only had to wait just under 2 seconds. In both of these cases, I saved a significant amount of time.

So what was the very small change that I made, and why did it radically reduce the amount of time for database creation and database restoration to occur? I turned instant file initialization on.

What is Instant File Initialization?

In my first two examples, before instance file initialization was turned on, the reason it took so long for the database to be created, or the database to be restored (before a database can be restored, its space must first be pre-allocated, much like creating a new database), SQL Server had to go to every page in the 50 GB database and zero each one of them out. It can take a lot of time for SQL Server to go to every 8K page in a file (especially very large files) and physically zero out each page. When instant file initialization is turned on, SQL Server doesn’t have to zero out every 8K page that has been allocated. Instead, the space is just allocated to SQL Server by the operating system in one fell swoop, which is a very quick process, potentially saving you a great deal of time.

How Do You Turn Instant File Initialization On?

Unlike most configuration features in SQL Server, there is no on/off switch for instant file initialization. Instead, you have to assign a specific user right to the SQL Server Service (mssqlserver) account. Here’s what you need to do to turn on instant file initialization.

First of all, to use instant file initialization with SQL Server in a production environment, you must be using some combination of:

  • Windows Server 2003 or
  • Windows Server 2008 or
  • Windows Server 2008 R2

and using:

  • SQL Server 2005 (any edition) or
  • SQL Server 2008 (any edition) or
  • SQL Server 2008 R2 (any edition)

Second, you must assign the SQL Server Service (mssqlserver) a special user right called “Perform volume maintenance tasks”. To do this, start the Local Security Policy tool (you must be a local administrator to perform this task), then drill down to Security Settings | Local Policies | User Rights Assignment | Perform volume maintenance tasks, as you see in the screenshot below.

Once you have located “Perform volume maintenance tasks”, right-click on it and select “Properties”, and the “Perform volume maintenance tasks Properties” screen appears. Click on “Add User or Group” and then proceed through the remaining screens until you select the account that is being used as the service account for SQL Server. In the screen shot below, notice that I have added the BRADMCGEHEE\sqlserverservice account to this user rights assignment. This is the user account I use on my test server to run my SQL Server instance.

Once the SQL Server service account has been assigned this user right, you will have to restart the SQL Server service (of course, only when it is not being used), and from this point forward, instant file initialization is turned on for all MDF files in your SQL Server instance.

Note: If your SQL Server service account is a member of the local administrators group, then the account already has the “Perform volume maintenance tasks” user right and you don’t need to assign it again.

Why Isn’t Instant File Initialization Turned On by Default?

When a SQL Server instance is first installed, one of the things you must enter is a SQL Server service account. If you follow the best practice and select a domain user account to be used as the SQL Server service account, the setup process automatically assigns the domain user account with only just enough rights and permissions to run SQL Server. The “Perform volume maintenance tasks” user right is not automatically assigned during installation because it is not required to run SQL Server, and because allowing the service account to have this additional user right introduces a very small security risk.

Oh no, a security risk! Well, not really much of a security risk. Here’s the possible security risk scenario. The disk that is being used to create the new database on has been used for storing data that has been previously deleted. As you may know, when data is deleted from disk by the operating system, it really is not physically deleted; the space holding the data is just marked as being available. At some point, the older data will be overwritten with new data. This occurs all the time on millions of computers throughout the world every day. And as such, any data that has been marked for deletion, but not yet overwritten, is potentially available for access if you have the right tools and know what you are doing. In fact, undelete software uses this to recover data that has been accidently deleted.

When instant file initialization is not turned on, and when SQL Server allocates space for an MDF file, each of the pages allocated for the database is zeroed out, which removes the older data, in theory, preventing it from being accessed. I say “in theory” because there are computer forensics techniques that can even recover data that has been overwritten, but that discussion is really not applicable here.

So if instant file initialization is turned on, there is a very slight risk that someone could go to the pages allocated for the new database and read any older data that still may exist there. This is essentially a non-issue in virtually every organization, other than those that require very high security. But because of this potential security issue, instant file initialization is not turned on by default.

If instant file initialization is turned on, and pages are not zeroed out when the database is initially created, SQL Server will automatically overwrite any data that might have been on those pages when SQL Server needs that space.

When Is Instant File Initialization Used?

If instant file initialization is turned on, it is used in all of these cases:

  • When a database is first created
  • When a an existing database’s size is manually increased
  • When tempdb is recreated each time SQL Server is restarted
  • When autogrowth kicks in
  • When backups are restored (as the space has to be pre-allocated before a restore can occur) 

Instant file initialization only affects MDF and NDF files, not LDF files. In other words, transaction log files can’t take advantage of instant file initialization. This is because log files are circular in nature and must be zeroed out, as random data in transaction log pages can be problematic. In my earlier test, when I created a new 50 GB database, the MDF file was 50 GB and the log file was only 1 MB. If I had created a large log file (which is not uncommon), it would have taken awhile for the log to be created, although the MDF file would have been instantly created. This is also true when you manually increase the size of a log file, or when log file autogrowth occurs. In other words, don’t expect to have all of your databases (MDF and LDF files) created in less than 2 seconds like in my test. While the MDF will be created virtually instantly, the log file may take awhile to be created.

When I was working with SQL Server 2000 a few years back, which does not support instant file initialization, one of the things that annoyed me the most when restoring large databases was waiting for the database space to be allocated before the restore actually began. During emergency database restores, this wasted a lot of precious time, preventing me from getting the database back into production as fast as I would have preferred. If you aren’t using instant file initialization today, you are facing this same problem. That’s why I recommend all SQL Server 2005/2008 instances have instant file initialization turned on. The time saved when restoring databases is the best reason to use instant file initialization.

Check to See if Your SQL Server Instances Have Instant File Initialization Turned On

Hopefully, by now, you see the benefits of using instant file initialization. Assuming that you don’t already know if instant file initialization is turned on or off on the SQL Servers your manage, I challenge you to check and see, and if you find it turned off, turn it on and reap its many benefits.

 

Test Hardware

  • Dell T610 Tower, with a single, 6-core CPU (Intel Xeon X5670, 2.93 Ghz, 12M Cache, HT, 1333MHz FSB); 32GB 1333MHz RAM; a PERC H700 RAID controller; two 146GB 15K SAS Drives; one dual-port HBA (to connect to the DAS); and dual network connections. Hyper-threading turned off.
  • One PowerVault MD3000 DAS with two, dual-port controllers, and 15 146GB 15K SAS drives. MDF files located on RAID 10 array with 10 spindles, LDF files on RAID 10 array with 4 spindles, backup drive on a single spindle.

 

 

 

 

Comments

Posted by Glenn Berry on 22 July 2010

Good post about an easy setting that many DBAs are not aware of. I completely agree about always enabling instant file initialization.

Hyper-threading seems to work much better for SQL Server workloads in the Nehalem and Westmere based Xeons (55xx, 56xx, 65xx, and 75xx) than it did on the old Pentium 4 based Xeons. It is always turned on for the submitted TPC-E benchmarks. You should try some of your testing with it turned on and off to see what effect it has.

Posted by Jose A. Hernandez on 22 July 2010

Just a note, instead of specifying the actual service account, Microsoft recommends granting it to the Local Machine User Group that gets created during the installation of SQL, SQLServer2005MSSQLUser$[SERVERNAME]$MSSQLSERVER.

That way, if you change service accounts credentials via the SQL Server Configuration Manager, the new service account will automatically be granted the rights to do Instant File Initialization.

Posted by Indianrock on 27 July 2010

I created a post about this on a Netapp forum because there are at least a few folks out there who don't think it applies on SAN/Smart disk devices.  Not that you can't turn it on, but that these devices are so fast or somehow "so smart" that it doesn't improve performance.  Our Systems group has the same answer about defragmenting such drives -- unnecessary.

communities.netapp.com/.../10280

Posted by cproctor on 27 July 2010

I wish more folks would do this:

SQL Server 2005 (any edition) or

SQL Server 2008 (any edition) or

SQL Server 2008 R2 (any edition)

Often, just like size, SQL edition matters.

Good post and will be checkig/testing this weekend.

Posted by Indianrock on 27 July 2010

If you're upsizing your database files manually, as you should be, rather than allowing autogrowth, then the restore factor is the main reason for this. How much difference in time would you estimate is involved in restoring a 500GB backup file?  

Randy

Posted by danielpila on 27 July 2010

very interesting article. I have a newbie question not related the article: at the test hardware the dell machine shows "Hyper-threading turned off. " ¿why this?

Posted by dbowlin on 27 July 2010

I did not know about this.   Nice article, thanks.

Posted by Tom Garth on 27 July 2010

This is without a doubt the best tip I have ever seen on any subject.

Thanks,

Tom Garth

Posted by Brad M. McGehee on 27 July 2010

Indianrock, I agree with you that DBAs should be manually managing their database files, not letting autogrowth do the work for them. As you said, if this is the case, the key benefit of using instant file initialization is restore time, although saving time creating a huge database still can be a nice time saver.

Posted by Brad M. McGehee on 27 July 2010

Danielpila, I use my test box for a variety of different tasks, and sometimes I have hyperthreading on, and sometimes off. In this particular case, it was off, and I just wanted to mention it for the sake of full disclosure. Whether it is on or off wouldn't have affected this particular test.

Posted by BobAtDBS on 28 July 2010

Good article, good research, well presented.  Thank you for doing such a great job with this.

Posted by free_mascot on 26 October 2010

Thank you, Brad. Very interesting and useful article.

Leave a Comment

Please register or log in to leave a comment.