Description

This wait occurs when a request is waiting to acquire a Key Range Insert Null lock with the low priority options added in SQL Server 2014. This mechanism acquires locks on a subset of rows in an index but only when the transaction isolation SERIALIZABLE is used. A null lock is used internally and is not documented by Microsoft. Values in this type are rare to non-existent. The low priority operations must not be set to abort blockers for this wait type to accumulate

Resolved by

DBAs and Developers

Suggested solutions

1. Ensure transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
2. If possible, run low priority tasks outside regular business hours. Using abort blockers will cause interruption to users and could cause application issues
3. Keep transaction durations short
4. Investigate enabling the READ_COMMITTED_SNAPSHOT database setting
5. Run trace/Profiler/Extended Event session in conjunction with Blocked Process Threshold to determine which queries are being blocked
6. Indexing, ensure long running queries and transactions are optimised
7. Ensure locks being escalated correctly: row, page or table (if table is partitioned considered enabling partition escalation)

Additional research

Key-Range Locking
Exploring Low Priority Lock Wait Options
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations

⇐ Back to index