Blog

The other day I was running a script I’d written to expand out some tables in the AdventureWorks sample database for some testing. The script was taking a long time, and at first I thought I was just spoiled because I usually run my tests on my laptop, whose storage is backed by SSDs. In this case I was running my test against a SQL Server instance up in AWS.

After a delicious fifteen minute coffee break, I realized something was wrong– it STILL wasn’t done. I grabbed sp_AskBrent® to do a quick check and see if my storage was really THAT slow.

I ran sp_AskBrent® with a 60 second sample, like this:

exec sp_AskBrent @seconds=60, @ExpertMode=1;
GO

Here’s what I saw (click for a larger version):

Lack of Instant File Initialization

PREEMPTIVE_OS_WRITEFILEGATHER means slow file growths

In a 60 second sample, I’d been waiting for PREEMPTIVE_OS_WRITEFILEGATHER for 36 seconds. Whoops!

The script I was running was making tables larger. The database was out of room, and was growing the data file in small autogrowth increments. Each of these automatic growths was stopping to “zero out” the files, because I hadn’t enabled Instant File Initialization in SQL Server.

How I enabled Instant File Initialization

My test instance is set up a bit uniquely– it uses UNC paths for the SQL Server databases. This is supported in SQL Server 2012, and I was testing it out in a Windows Failover Cluster. (If you use shares for files, you need to be very careful about redundancy and availability of course, but that’s a topic for another post.)

I’d forgotten to enable Instant File Initialization for the SQL Server Service on the server that hosted the file shares! Wherever Windows will actually grow the files, the SQL Server Service account needs the “Perform Volume Maintenance” tasks right.

Using the Local Security Policy to Grant “Perform Volume Maintenance Tasks”

Here’s how to grant the rights:

First, open the local security. I usually do this by running the command secpol.msc, but you can also find this in the “Administrative Tools” menu.

Opening the Local Security Policy

Then navigate to Local Policy -> User Right Assignment -> Perform Volume Maintenance Tasks.

Perform Volume Maintenance Tasks

Add the SQL Server Service account. The change will take effect the next time you restart SQL Server.

Grant IFI to the SQL Server Service Account

So, did it improve my performance?

Heck yeah, it did! On the first run I got tired of waiting and killed my expansion script after 20 minutes and just dealt with the rollback. After enabling Instant File Initialization on fileserver that holds the shares where my database files live, the expansion script finished in four minutes and 48 seconds. Shazam!

↑ Back to top
  1. Hi Kendra

    Great article, Have you ever seen this wait type when doing backups to a unc disk?

    • Hi Paul,

      I haven’t personally observed it — I believe I primarily usually see waits that have ‘backup’ in the name when those occur. (Just going from memory, haven’t reproduced.)

      Kendra

      • Hi, i have just observed that kind of wait type in a restore process. It was waiting in PREEMPTIVE_OS_WRITEFILEGATHER event. DB size is about 10 TB so i thought that it was not normal after waiting about one hour. Then i ‘ve checked sys.dm_exec_requests i’ve seen percent_complete is still 0. I’ve checked the disk activity from resoruce monitor. I’ve seen only two disk activities called “D:\$LogFile (NTFS Volume Log) and D:\$Mft (NTFS Master File Table)” There was nothing in my restore location so i understand that there was a problem in my OS and i’ve asked mr google :) thant i ‘ve find that article. My mssql server service is running under NT Service\MSSQLSERVER. I’ve added that into policy and BINGO:)

  2. Hello Kendra

    Nice article. Sure Instant File Initialization is a great thing since SQL Server 2005 / Windows 2003 Server, even greater if it worked also for the log files, not just the mdf/ndf data files. Some how, this great feature seems to be not very popular (imho), perhaps due to some security implications.

    • Interesting! What security concerns have folks cited?

      • Only security issue I’ve seen specifically called out is the lack of 0 initialization in conjunction with dbcc page. Specifically, if the data file grows into space previously allocated, but was soft deleted (where the address pointers were merely deleted and the space was added back into available storage), you can use dbcc page to interrogate the previously stored contents. Not sure if I buy it as a realistic security concern unless the storage is shared and unencrypted. Additional forewarning, I have not played with this in a true SAN environment (especially VM environment) to see if you can do this with unallocated blocks at that level. My gut feeling is that it would only be relevant in a thin provisioned scenario/dynamic vhd(x) – which I would personally argue against in the first place.

        • If you’re looking for an extra excuse to not allow application accounts the sysadmin right, I guess this adds yet another thing to the long list of reasons why that’s a terrible idea. :)

  3. Really only one security concern with Instant File Initialization. Which is the reason it’s not enabled by default–
    If you enable IFI, and thus don’t have it zero-out all new storage being allocated, your new space can include data that was sitting there from before, like say old Emails or whatever else you had on disk. Which is visible when the file (or its backup) is examined with say a hex tool. For a disk pool that’s never been used for anything but your SQL data, I don’t usually see a problem. For mixed-usage disk, or say in HIPAA compliance situation, it could matter.
    I’m wondering if AWS disk might be a special case; do they already zero it for you when it gets presented to you?

    • I’ve never found any customers who were actually *concerned* about the security risks of IFI when it was all explained– hence me wondering if there really are folks out there for whom this is a big concern.

      Perhaps the people who would be concerned about instant file initialization’s security implication would tend to use Transparent Data Encryption (which means IFI doesn’t work anyway)?

  4. Excellent point– Anyone who’s likely to be concerned with IFI data leakage SHOULD be using TDE.

  5. I have no comment…. just trying to sign up for the newsletter

    • If you scroll down just a LITTLE bit more, there’s a bar to input your email address at the very bottom of every page– that’ll get you signed up for the newsletter.

  6. Pingback: Slow Restore Operation on New Server and Set Instant File Initialization (IFI) | Jingyang Li (Limno)

  7. Pingback: Jingyang Li (Limno)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php