BACKUPTHREAD

Description

Usually occurs while waiting for some I/O to complete on a thread, like zero initialization of a data or log file. Note that all backup waits are accumulated for restore operations too

Resolved by

DBAs

Suggested solutions

  1. Look out for PREEMPTIVE_OS_WRITEFILEGATHER as this means that the data or log file are being zeroed out in a restore operation. Investigate implementing Perform Volume Maintenance Tasks to avoid zero-initialization for data files
  2. Decrease size of transaction log being restored (less storage to zero-out)

Additional research

Backup Wait Statistics
Improving SQL Server Performance by using Instant File Initialization

See more

Check out SpotLight, a free, cloud based SQL Server monitoring tool to easily detect, monitor, and solve SQL Server performance problems on any device

 

⇐ Back to index