“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 doing 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.
For additional help on locks and lock contention, check the following sources:
“Types of Locks” in the System Administration Guide provides information about types of locks to use at server or query level.
“Reducing lock contention” provides pointers on reducing lock contention.
Chapter 8, “Indexing for Performance,” 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.
