Published 31 January 2017
A transaction is waiting to acquire an intent exclusive lock. When granted at one level in the database hierarchy, this lock protects acquired or requested exclusive and shared locks on certain objects at lower levels. For example, when placed at the table level, it indicates that a transaction intends to place an exclusive lock on pages or rows in that table. These prevent other transactions from accessing the objects, so no other processes can read or modify data.
The SQL Server Database Engine uses locking to manage the way multiple users access the same data at the same time. Every transaction must first request a lock on the resource it wants to modify. This lock stops other transactions from modifying the same resource in a way that might cause conflict. The lock is only released once the transaction no longer needs it. For more information, including details of lock type compatibility, see SQL Server Transaction Locking and Row Versioning Guide (TechNet).
Lock waits commonly occur on busy servers where concurrent transactions demand the same resource, resulting in poor performance. A high number of locking waits may indicate blocking problems and should be investigated.
Break long transactions down into shorter ones. See Managing Long-Running Transactions (TechNet).
Check the isolation levels for your transactions and update the locking and row versioning behavior if necessary. See Set Transaction Isolation Level (TechNet).
Check whether lock escalation is causing blocking problems and resolve if necessary. The Average wait time tells you whether you’re suffering from many short blocks or several long blocks. To troubleshoot blocking, see How to resolve blocking problems that are caused by lock escalation in SQL Server (Microsoft Support).
Check the affected queries and the Top queries table. Tune queries so they run faster and require fewer locks. See Query tuning (TechNet).
On the Analysis page, check these metrics for additional details on locking behavior:
Avg. lock wait time
On the Analysis page, check these metrics to see whether memory problems or I/O bottlenecks are causing locks to be held for longer than usual:
Machine: memory used
Disk avg. read time
Disk avg. write time
Buffer cache hit ratio
Buffer page life expectancy
See also Investigating I/O bottlenecks.
Check the sys.dm_trans_locks DMV for resources associated with lock requests. See sys.dm_tran_locks (TechNet).
Consider using partitions to split single lock resource into multiple resources (only available if you’re using more than 16 CPUs). See http://technet.microsoft.com/en-us/library/ms187504(v=sql.105).aspx.