Usually when data file grows or when you create a new file for it or whatever else, SQL will have to zero out the disk that that file is added on or grown out to. It zeros, and that can be a really slow process depending on the size of the growth, and what else is going on on that disk at that moment and also the speed of the disk itself.

Full Transcript:

Hello there, children. My name is Erik, I’m with Brent Ozar Unlimited, and today we’re going to be talking about Instant File Initialization or IFI. Now, usually when data file grows or when you create a new file for it or whatever else, SQL will have to zero out the disk that that file is added on or grown out to. It zeros, and that can be a really slow process depending on the size of the growth, and what else is going on on that disk at that moment and also the speed of the disk itself. If you have a very slow disk so very large growth can take a very long time. With Instant File Initialization or IFI turned on, Windows – or rather, SQL Server just goes ahead and gives that space a big hug, says I’m going to use this eventually Windows, don’t worry about it, I got this, we will write some data here when we get to it.

So the data file grows because you’ve reached some internal threshold of capacity in your current data file size. And SQL Server knows it needs to grow out to add more data because you don’t want to just stop growing because then you get all sorts of errors about we couldn’t grow – we couldn’t add data to your data file and errors and database goes into recovering or recovery pending or some other awful state that you don’t want to deal with. So this is a really nice way to just make that process faster.

Instant File Initialization is a Windows account based policy; it’s not something that you technically set inside SQL Server. It’s the perform volume maintenance tasks option, which is in your security policy snap-in. It only applies to data files; it does not apply to log files. So when a log file grows that still has to happen with the zeroing out. The disk is only written to when there’s new data. So when you create a database, add files to a database, grow a database or restore a database file group, that’s when Windows can use Instant File Initialization.

Now, no discussion about Instant File Initialization is quite complete without going into what some people say is a security risk that goes along with Instant File Initialization. Some people will tell you that since you don’t zero that space out that your data file grew into. If you had some information on that disk, on that space that the data file gave a big hug to, if someone came along and they got on your server and say they shut down SQL Server and then they opened up your data file with a hex editor, they might be able to see information in the data file that used to inhabit the disk that the data file gave a big hug to that space.

Well, that’s valid, that’s totally valid, I just think that if you have someone with malicious intent that has shut down your SQL Server and has your data file open with a hex editor, you have much bigger problems than what used to be on that disk. That’s just me though. So the question usually is, should I enable Instant File Initialization? And in most cases, the answer is yes. Now, if you want a really easy way to learn how to do that, what I usually do is I pop on the server – I know, I remote desktop, I’m a horrible DBA – and I open up the SQL Server configuration manager. What that tells me for the SQL Server and for the SQL Server agent is what account they log on is. So in this case, this is just my local computer and that’s my weird Windows username that they gave me. EDarl_000, like not James Bond, 007. It’s awful. So I go there and I find the log on name there. If you’re using a service account that will be your service account, if you’re using built-in accounts it will look like this or whatever.

If you just go to the start menu and write in SCCPOL.MSC so SCCPOL.MSC, which is a security policy, you should see something like this pop-up, and if you expand local policies and go into user writes assignment, you scroll down a little bit and there is perform volume maintenance tasks. That is what you want to get into, and the properties here, you’ll see I have a bunch of stuff in here, but not EDarl_000. That’s because EDarl_000 was a member of the administrator’s group. So he inherits the perform volume maintenance task privilege from this group. I also have the NT service here because I’m a little superstitious about that. If you’re using the built-in service account, I always like to add them here just to be double extra triple sure that I’m not going to get Instant File Initialization.

Now, that really does make file growth much, much faster. It’s the difference between setting, you know, zero out potentially gigs of data, especially if, you know, you’ve not done your job and you left a data file in percent auto-grow and it’s a terabyte and now it’s growing by 10% at a stretch and well, that’s a pretty big growth. You do have to restart SQL in order for this to kick in, which is sort of a bummer, but it’s okay because you’re a good DBA, you’re learning this stuff and you take regular maintenance windows to patch SQL anyway. So the next time you install that service pack or cumulative update or Windows update, you’ll add the perform volume maintenance tasks privilege, then restart your data files will be so happy with.

Now, kind of new in SQL Server 2016 and up, this became something you can do in the install process. So if you are rolling out SQL Server 2016, pay really careful attention during the setup process where there is now a little checkbox. This wasn’t there before. This was always an afterthought, this was a step that you had to do post install a task. Now it’s just in the setup, which is awesome, along with – also in 2016 is you can set up tempdb the way you want during the setup process. You don’t have to go and goof around with scripts and what not afterwards. So this is there now and it’s nice, just pay attention, make sure you check that box no matter what account you’re using so that when you start growing files or restoring databases or creating databases, it’s happening as quickly as possible. Thanks for watching, I hope you learned something. If you didn’t, I apologize; it’s just not really all that much more to say about Instant File Initialization. Bye.