Sybase ADDICT

Adaptive Server includes a set of system tables that contains monitoring and diagnostic informations. Known as “MonTables” or MDA tables, they provide a “statistical snapshot” of the state of ASE. You can query these system tables pretty much as the same way you query any other tables.

Here is a quick HowTo to set them up.

First, check that the configuration parameter ‘enable cis’ is set to 1

sp_configure 'enable cis', 1
go

Add ‘loopback’ server name alias

use master
go
sp_addserver loopback, null, @@servername
go

Install the MDA tables.

isql -U sa -P yourpassword -S YOURSERVER \
-i $SYBASE/$SYBASE_ASE/scripts/installmontables

Assign ‘mon_role’ to logins allowed MDA access

use master
go
grant role mon_role to sa
go

Test basic MDA configuration:

select * from master..monState
go

Now enable all configuration parameters:

sp_configure "enable monitoring", 1
go
sp_configure "sql text pipe active", 1
go
sp_configure "sql text pipe max messages", 100
go
sp_configure "plan text pipe active", 1
go
sp_configure "plan text pipe max messages", 100
go
sp_configure "statement pipe active", 1
go
sp_configure "statement pipe max messages", 100
go
sp_configure "errorlog pipe active", 1
go
sp_configure "errorlog pipe max messages", 100
go
sp_configure "deadlock pipe active", 1
go
sp_configure "deadlock pipe max messages", 100
go
sp_configure "wait event timing", 1
go
sp_configure "process wait events", 1
go
sp_configure "object lockwait timing", 1
go
sp_configure "SQL batch capture", 1
go
sp_configure "statement statistics active", 1
go
sp_configure "per object statistics active", 1
go
sp_configure "max SQL text monitored", 2048
go

And you’re done

Please note under some versions (v12.5.3 and above) and some particulars occasions, parameter “per object statistics” may leads to time slice errors. Was a bug fixed in v15.x.

To disable the use of MonTables, just disable monitoring this way:

sp_configure 'enable monitoring', 0
go

More to come about it later, stay tuned…