“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.
See “Identifying tables where concurrency is a problem” on page 88 of Performance and Tuning: Locking.
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” on page 40 of Performance and Tuning: Locking provides pointers on reducing lock contention.
Chapter 13, “Indexing for Performance,” in Performance and Tuning: Basics, 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.
