PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)

SQLTop*, lightweight, simple and easy to use.

When a performance issue occurs, SQLTop* will show quick and easy entire SQL Server Performance on real-time, including waits, performance counters and sessions.

Source : https://www.brentozar.com/archive/2014/02/preemptive_os_writefilegather-and-instant-file-initialization-ifi/

PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)

by Kendra Little February 13, 2014

25 comments

The other day I was running a script I’d written to expand out some tables in the AdventureWorkssample 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:

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

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

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.

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

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

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!