Version 1

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

     

    ALERT DEFINITION

    To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Multiple Numeric Return. Configure the alert with values similar to these:

     

    Alert Name: Sybase Spinlock Contention

     

    Execution Interval: 10 minutes

     

    Notification Text: The following servers are experiencing spinlock contention

     

    SQL Statement:

    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

     

    Execute Against: Monitored Instance

     

    Units: % (contention percent)

     

    High Threshold: Min 90, Max empty

    Medium Threshold: Min 80, Max 90