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

Sybase custom monitoring using SAM

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.

PARAMETERDESCRIPTIONSAM custom query
ASE_StatusMonitors 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
BackupServerStatusMonitors 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"
ErrorLogUpdatesReports 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
NumBlockedProcsMonitors 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
SuspectDatabasesDetermines 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)
ConnectionsUsedPCTDisplays 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
LocksRemainingMonitors 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
OpenIndexesUsedPCTReports 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
OpenObjectsUsedPCTReports 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
DatabaseSpaceUsedPCTMonitors 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
LogSpaceUsedPCTMonitors 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
SuspectIndexIdentifies 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
EngineBusyAvgPctReports 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.

 

 

 

Tags (2)
0 Replies