SAM able to monitoring Sybase instances using ODBC driver. It supports all supported version and it's very light weight
1. Agentless monitoring. So no maintenance work on target side.
2. No vulnerability, local host level user and password management issues are eliminated.
There are many out-of-box baselines available but again all are not critical parameters to get it monitored. It depends on the environment this can be defined.
We compared with existing monitoring baseline to match and replace. During comparison we found only 2 out of 13 are out-of-box and rest everything should be done via customization.
Below are the parameters and custom query build with the help of SAM and internal engineers.
| PARAMETER | DESCRIPTION | SAM custom query |
| ASE_Status | Monitors the status of the Sybase Adaptive Server Enterprise. The parameter value is zero (0) when the ASE is up and running. | select count(*) from master..syslisteners |
| BackupServerStatus | Monitors the status of the Sybase backup server. The parameter value is zero (0) when the backup server is up and running. | select count(*) from sysservers where srvname = "SYB_BACKUP" |
| ErrorLogUpdates | Reports errors in the Sybase ASE error log. The parameter value is zero (0) when there are no errors. | Enable MDA functionality Enable monErrorLog Enable MDA functionality as follows. -- "enable monitoring" is the 'master switch' for -- most MDA functionality (although some things, -- like monState (above) are always enabled when the -- MDA tables are installed. -- Setting "enable monitoring" to 0 will disable any -- MDA features that could have any performance impact -- on your ASE server. -- sp_configure "enable monitoring", 1 go Enable monErrorLog - to get populated. -- this enables monErrorLog: sp_configure "errorlog pipe active", 1 go sp_configure "errorlog pipe max messages", 100 go 0 |
| NumBlockedProcs | Monitors the total number of processes that are currently being blocked by another process. | select count(*) from master..sysprocesses where suid > 0 and spid <> @@spid and status = 'lock sleep' and blocked > 0 and time_blocked > 300 go |
| SuspectDatabases | Determines which databases, if any, have been marked as suspect or unusable by the ASE. Suspect databases are those databases marked as unrecoverable and cannot be opened or used by the ASE. | select count(*) from master..sysdatabases where (status & 256 = 256) or (status & 64 = 64) |
| ConnectionsUsedPCT | Displays the percentage of user connections being used on the server. | Select round(cast(Connections as float)/cast(@@max_connections as float)*100,0) from monState |
| LocksRemaining | Monitors the number of locks available for use. | create procedure sp_swLocksRemaining as declare @LRret_value int, @free_locks char(6) execute @LRret_value = sp_monitorconfig 'number of locks', swLocksRemaining select @free_locks = Pct_act from swLocksRemaining truncate table swLocksRemaining select @free_locks |
| OpenIndexesUsedPCT | Reports the percentage of open indexes currently being used. | create procedure sp_swOpenIndexesUsedPCT as declare @OIret_value int, @OpenIndex_pct char(6) execute @OIret_value = sp_monitorconfig 'open indexes', swopen_indexes select @OpenIndex_pct = Pct_act from swopen_indexes truncate table swopen_indexes select @OpenIndex_pct |
| OpenObjectsUsedPCT | Reports the percentage of open objects currently being used. | create procedure sp_swOpenObjectsUsedPCT as declare @OOret_value int, @Openobject_pct char(6) execute @OOret_value = sp_monitorconfig 'open objects', swopen_objects select @Openobject_pct = Pct_act from swopen_objects truncate table swopen_objects select @Openobject_pct |
| DatabaseSpaceUsedPCT | Monitors the amount of total space allocated to the database versus database size. | create table #tdatasize ( Server nvarchar(19) ,DB nvarchar(20) ,DataFree_perc int ,LogFree_perc int ) insert into #tdatasize select "Server"=convert(char(19),@@servername), "DB"= db_name(d.dbid), "DataFree_perc"=ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 and vdevno >= 0 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))), "LogFree_perc"=ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) and vdevno >= 0 then u.size end))) from master..sysdatabases d, master..sysusages u where u.dbid = d.dbid and d.status != 256 group by d.dbid order by db_name(d.dbid) select max(DataFree_perc) from #tdatasize drop table #tdatasize |
| LogSpaceUsedPCT | Monitors the remaining amount of space used by the syslogs table (the transaction log) of the current database. The syslogs table of each database holds all database changes (for example, updates, inserts, and deletes) since the last database or transaction log dump. If the syslogs table is not assigned to its own device, the parameter can be deactivated. In this case, monitoring the database expansion space can determine the space available for the transaction log because it shares the same device as the database. PATROL uses the Transact-SQL function data_pgs to determine the transaction log space. | create table #tlogsize ( Server nvarchar(19) ,DB nvarchar(20) ,DataFree_perc int ,LogFree_perc int ) insert into #tlogsize select "Server"=convert(char(19),@@servername), "DB"= db_name(d.dbid), "DataFree_perc"=ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 and vdevno >= 0 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))), "LogFree_perc"=ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) and vdevno >= 0 then u.size end))) from master..sysdatabases d, master..sysusages u where u.dbid = d.dbid and d.status != 256 group by d.dbid order by db_name(d.dbid) select max(DataFree_perc) from #tlogsize drop table #tlogsize |
| SuspectIndex | Identifies suspect indexes. After a sort order change, the parameter looks for indexes that have been marked as suspect in the master..sysindexes table. | select count(*) from sysindexes i where (i.status & -32768) != 0 |
| EngineBusyAvgPct | Reports the average active time for all ASE engines. | create procedure sp_swUserBusyAvg as select isnull(100.0 * convert(real, u.value) / t.value, 0) 'UserBusy', isnull(100.0 * convert(real, s.value) / t.value, 0) 'SystemBusy', isnull(100.0 * convert(real, io.value) / t.value, 0) 'IOBusy', isnull(100.0 * convert(real, i.value) / t.value, 0) 'Idle', t.group_name into #tmpUserBusyAvg from master..sysmonitors u, master..sysmonitors s, master..sysmonitors io, master..sysmonitors i, master..sysmonitors t where u.group_name = t.group_name and s.group_name = t.group_name and io.group_name = t.group_name and i.group_name = t.group_name and u.field_name = 'user_ticks' and s.field_name = 'system_ticks' and io.field_name = 'io_ticks' and i.field_name = 'idle_ticks' and t.field_name = 'clock_ticks' and t.value > 0 select avg(UserBusy) as 'UserBusyAvg' -- ,avg(SystemBusy) as 'SystemBusyAvg' -- ,avg(IOBusy) as 'IOBusyAvg' -- ,avg(Idle) as 'IdleAvg' from #tmpUserBusyAvg drop table #tmpUserBusyAvg |
It can help to those who are looking for Sybase Monitoring solution.
Please share your feedbacks.
