“Logical Lock Contention” reports the number of times a task was switched out due to contention for locks on tables, data pages, or data rows.
Investigate lock contention problems by checking the transaction detail and lock management sections of the report.
See “Transaction Detail” and “Lock Management”.
Check to see if your queries are performing deferred and direct expensive updates, which can cause additional index locks. See “Updates”.
Use sp_object_stats to report information on a per-object basis.
See Chapter 3, “Locking Reports,” of Performance and Tuning Series: Locking for more information.
For additional help on locks and lock contention, check the following sources:
“Types of Locks,” in the Performance and Tuning Series: Locking and Concurrency Control provides information about types of locks to use at server or query level.
See chapter 2, “Locking Configuration and Tuning,” of Performance and Tuning Series: Locking for information about reducing lock contention.
Chapter 1, “Introduction to Locking,” in Performance and Tuning Series: Locking provides information on indexes and query tuning. In particular, use indexes to ensure that updates and deletes do not lead to table scans and exclusive table locks.