Instant File Initialization on Shared drive

Question

I asked something similar to this before, but previously I had asked about moving backups to a shared location. This time I am curious: if I want to restore a database on a shared drive, do I need to enable IFI on that server or just the server that is running SQL Server?

The reason I ask is I am restoring a pretty large database and its been stuck at 100% restored for the last couple hours. The wait type in sp_whoisactive is:

(28472716ms) `PREEMPTIVE_OS_WRITEFILEGATHER.

The only time I've seen that is when IFI is not on, but I do have it enabled on the SQL Server, but it is not enabled on the shared drive server.

Answer 1

_{Community Wiki answer generated from comments on the question, from Bob Klimes, Sean Gallardy, and Aaron Bertrand.}

---

You'll need to enable Instant File Initialization (IFI) on the server holding the disk. Restart SQL Server for it to take effect.

You can test if IFI is working on the share by creating a database on the share with trace flags 3004 and 3605 turned on. Output is written to the SQL Server error log.

If IFI is working, there will not be any entries for zeroing the mdf file.

IFI does appear to work with network shares, but for me the creation of a test database took 45 seconds on the share and less than 1 second locally. Even with IFI enabled, you might still have performance problems.