Description
This wait occurs when a request is waiting to acquire an Exclusive lock with the low priority options added in SQL Server 2014. This wait typically occurs when read and modify requests are blocked by another write transaction (implicit of explicit) that has been kept open for an extended period of time. Some values are expected but if values are excessive please see suggested solutions below. The low priority operations must not be set to abort blockers for this wait type to accumulate
Resolved by
DBAs and Developers
Suggested solutions
- 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
Exploring Low Priority Lock Wait Options
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations