A few days ago, I was running a large table copy from one database to another. Something like this:

This is a huge table, it has close to 6 billion records and the table is close to 300 GB in size. As the copy progressed, I see a lot of I/O going from one drive to the other. But every few minutes, I/O goes down to zero. It stays there for a few minutes and then revamps to higher numbers. Again after a few minutes, I see the same pattern. On further digging in, I noticed that everytime I/O drops, the SPID is waiting on PREEMPTIVE_OS_WRITEFILEGATHER. Now it makes sense. PREEMPTIVE_OS_WRITEFILEGATHER indicates the Auto Growth event. Looks like as the table copy happens, the database files run out of space and Auto Growth kicks in to increase the file sizes. Since this is a large database, the 10% increase is close to 100 GB and that takes a lot of time. We have a couple of options here:

• Make sure the database files are pre-sized correctly, so the chances of auto growth are minimized. Assign the size ahead of time, so Sql Server can get contiguous spaces on disk, which in turn makes it faster.
• Reduce auto growth from percent to a fixed number. Something like 1 GB every time (rather than 10%). But I do not recommend this. This create note fragmentation and in my opinion causes more harm than good. But this may be a good option for some environments.
• Enable Instant File Initialization. This is an amazing feature that I wrote more about in my previous post. This allows Sql Server to skip the step of zeroing out the new allocated disk space before using.

This PREEMPTIVE_OS_WRITEFILEGATHER wait could occur in other similar situations that require allocation of new space. For example, database restores.

Hope this helps,

_Sqltimes