Custom Ignite Alert: Sybase Spinlock Contention#LR14352
When modifications are made to a cache in Sybase, a spinlock (or mutex) denies all other tasks access to the cache while the changes are being made. Although spinlocks are held for extremely brief durations, they can slow performance in systems with high transaction rates. If spinlock content ion is more than 10%, consider using named caches or adding cache partitions.
This custom Ignite alert will return a list of all caches along with their spinlock contention which Ignite will then compare to the thresholds defined in the alert. In the example below, a warning email will be sent when the spinlock contention is between 5-10% and a critical email will be sent when it is above 10%.
The alert definition is shown below and here is the SQL statements that can be plugged into the Ignite alert:
dbcc monitor('select', 'spinlock_s', 'on') dbcc monitor('select', 'spinlock_p', 'on') dbcc monitor('select', 'spinlock_w', 'on') dbcc traceon(8399) select P.field_name as spinlock_name, case P.value when 0 then 0.0 else (100.0 * W.value) / P.value end as contention_percent -- case W.value when 0 then 0.0 else S.value / W.value end as spins_per_wait -- P.value as grabs, -- W.value as waits, -- S.value as spins from master..sysmonitors P, master..sysmonitors W, master..sysmonitors S, master..sysconfigures co where P.group_name = 'spinlock_p_0' and W.group_name = 'spinlock_w_0' and S.group_name = 'spinlock_s_0' and P.field_id = W.field_id and P.field_id = S.field_id and P.field_name = convert(varchar(30), co.name) and co.parent = 19 and co.config = 19 order by 2 DESC
Ignite alert definition