Description

This wait occurs when a request is waiting to acquire an Intent Shared lock with the low priority options added in SQL Server 2014. These locks are required to protect the object or data of other requests. For example when a query is run on a table an Intent Shared lock is placed on the table and database itself, this allows other requests to read the table but requests to modify the table or database (eg rename) will be blocked. This wait typically occurs when requests are blocked by another write transaction (implicit of explicit) that has been kept open for an extended period of time. The low priority operations must not be set to abort blockers for this wait type to accumulate

Resolved by

DBAs

Suggested solutions

1. If possible, run low priority tasks outside regular business hours. Using abort blockers will cause interruption to users and could cause application issues
2. Keep transaction durations short
3. Ensure that transactions isolation levels are appropriate (avoid SERIALIZEABLE and REPEATABLE READ if possible)
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 that long running queries and transactions are optimised
7. Ensure that locks being escalated correctly: row, page or table (if table is partitioned considered enabling partition escalation)

Additional research

Exploring Low Priority Lock Wait Options
Lock Modes
Reducing SQL Server Locks
Understanding Isolation Levels
Identify the Cause of SQL Server Blocking
Lock Escalations

⇐ Back to index