cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Custom Alert - Sybase Spinlock Contention

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

Labels (1)
Comments

Running it on ASE 15.5 (EBF21264) and get negative %...

Version history
Revision #:
1 of 1
Last update:
‎01-10-2014 12:13 PM
Updated by: