Blog-Post

SAP Sybase ASE wait events metric

May 21, 2013

Monitoring tables in SAP Sybase ASE are a valueable tool to detect bottlenecks (e.g. locks, physical i/o). The “wait event” metrics show the causes for bottlenecks. This is about how we got the best out of this metric.

screenshot cacti

The graph show the number of seconds (“m” meaning milliseconds) per second a wait event of this type/class lasts.

wait events

ASE documentation says: “A wait event occurs when a server process suspends itself, sleeps, and waits for another event to wake it. Adaptive Server includes unique wait event IDs for each of these wait events.”

The “wait events” can be grouped together via “WaitClassID” with a simple join:

  select
  	sum(convert(bigint,msw.WaitTime)) WaitTime,
  	sum(convert(bigint,msw.Waits)) Waits,
  	mwi.WaitClassID,
  	mwci.Description
  from
  	master..monSysWaits msw,
  	master..monWaitEventInfo mwi,
  	master..monWaitClassInfo mwci
  where
  	msw.WaitEventID = mwi.WaitEventID and
  	mwci.WaitClassID=mwi.WaitClassID and
  	mwi.WaitClassID<=10
  group by mwi.WaitClassID,mwci.Description
  order by mwi.WaitClassID ASC

You need to have mon_role to execute this and option “enable monitoring” and “wait event timing” has to be configured to “1” (via sp_configure)

cacti

Finally we wrapped the sql-statement above into a small script which maps ClassIDs to field names and feeds the data into cacti every 5 minutes.

This gives a meaningful graph to see with a quick lock wether and when for example locks or disk i/o are the bottleneck. So you get a good starting point where to look when diving deeper via monitoring-tables.

possibilities

Currently we’re thinking in integrating these metric into zabbix monitoring, so that we’re able to trigger events when metrics are crossing watermarks.