Description

 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 contention is more than 10%, consider using named caches or adding cache partitions.

This 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%.

SQL Statement

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

Alert Definition