Lock Detail

Provides information that helps you determine whether an application is causing a lock contention or deadlock-related problem.

Lock contention can have a large impact on Adaptive Server performance. Table locks generate more lock contention than page or row locks because no other tasks can access a table while there is an exclusive table lock on it, and, if a task requires an exclusive table lock, it must wait until all shared locks are released. If lock contention is high, run sp_object_stats to help pinpoint the tables involved.

“Lock Detail” reports locks by type:

“Lock Detail” reports on the values for Granted and Waited for these lock types:

“Lock Detail” reports on Avg Hash Chain Length, Lookups, and Spinlock Contention for these lock types:

You can configure the size of the lock hash table using lock hashtable size. If the average number of locks per hash chain is more than four, consider increasing the size of the hash table.

The “% of total” column displays the percentage of the specific lock type that was granted or had to wait with respect to the total number of lock requests.

Large inserts with bulk copy are an exception to this guideline. Lock hash chain lengths may be longer during large bulk copies.

See Chapter 2, “Locking Configuration and Tuning,” in Performance and Tuning Series: Locking and Concurrency Control, and Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide, Volume 1.