Description
Occurs while SQL Server is waiting for the Operating System to complete portions of write operations. This wait is commonly seen in databases with auto-growth not configured appropriately. Small values are expected. Excessive values should be investigated
Resolved by
DBAs and Developers
Suggested solutions
- Enable Perform Volume Maintenance Tasks to avoid zero-initialisation for data files
- Increase the auto-growth from the default (less frequent growths)
- Investigate storage issues in the Windows Event Viewer
- Upgrade storage
- Consolidate transactions from row-by-row statements to batch processing
Additional research
PREEMPTIVE_OS_WRITEFILEGATHER and Instant File Initialization (IFI)
Some Initial Insert Test Benchmarks on SQL Server 2008 R2
PREEMPTIVE and Non-PREEMPTIVE