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:
Granted – the number of times that each lock type was immediately granted to the application.
Waited – the number of times that each lock type waited a particular lock type.
Lookups –
Avg Hash Chain Length – the average number of locks per hash bucket during the sample interval.
Spinlock Contention – number of times an engine encountered spinlock contention.
“Lock Detail” reports on the values for Granted
and Waited
for
these lock types:
Exclusive Table
Exclusive Intent
Shared Intent
Exclusive Page
Exclusive Row
Update Row
Shared Row
Shared Address
Last Page Locks on Heaps
“Lock Detail” reports on Avg Hash Chain Length, Lookups, and Spinlock Contention for these lock types:
Shared Table
Page & Row Lock HashTable
Update Page
Shared Page
Next-Key
Address Lock Hashtable
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.