When restoring database, why would zeroing log file take a long time?

Question

I am building a new development server that will get weekly automated restores of production backups. This server will be replacing an existing development server where I have this process running smoothly for a few years.

The issue I am facing is that when restoring my production backups to this new server, it takes forever to zero the log file. When I restore the same production backups to the existing dev server, log file zeroing takes a lot less time.

It's happening on all database backups, but here's one example. The log file is about 56GB in size and has 281 VLFs.

As you can see from the logs, it's taking about 2 minutes to get to 100% in the reported restore process, then it's taking an additional 4 minutes to zero the log file. The wait while log file zeroing is happening is PREEMPTIVE_OS_WRITEFILEGATHER. 4 minutes is not a big deal here, but with my larger databases, the wait time is significantly longer.

Shrinking the log file down to nothing then doing the backup/restore eliminates the zeroing time, but that's not a viable option as the log file will grow again. Even when I shrunk it down to nothing and re-grew it to 56GB using 8GB chunks resulting in 116 VLFs, that sill has the same excessive time to zero the log file.

And again, restoring this same backup file to another server requires much less time to zero the log file. Here's what restoring the same backup on the existing dev server looks like, zeroing takes about 30 seconds:

Any idea what's going on here?

Answer 1

Any idea what's going on here?

One server has better IO than the other, probably due to the disk that it's own or the hardware that's in it - or if in the cloud host or disk based restrictions.

In the one labeled dev, it's doing ~40 MB/sec zeroing the file, whereas in the one labeled prod, it's doing ~227 MB/sec. There are various things that could be hurting it, but we have no additional data.

What are the server spec differences? What's the underlying IO in each server? What are the connections? Are the servers VM, if so what's the storage backend and disk type along with the vm config and any overprovisioning, etc.? What does a minifilter capture and a storport capture say? What do the perfmon disk counters look like in terms of throughput and latency? We'd need to know this type of information and potentially more to reach a positive conclusion.