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
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