Description
Occurs while waiting for non-data-file disk I/O to complete. For example while growing a transaction log disk I/O is required to zero-out the file. Backup and restore operations can also accumulate this wait counter. As this wait accumulates during backups moderate values are expected
Resolved by
DBAs and Developers
Suggested solutions
- If possible, run low priority tasks outside regular business hours. Using abort blockers will cause interruption to users and could cause application issues
- Keep transaction durations short
- Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
- Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
- Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
- Indexing, ensure long running queries and transactions are optimised
- Ensure locks being escalated correctly: row, page or table (if table is partitioned considered enabling partition escalation)
Additional research
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations