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
