How to Retrieve Key Performance Metrics from SAP ASE / Sybase Instances?

[HarshPatel1]

I want to fetch important performance metrics from SAP ASE/Sybase databases. These include:

1. Percent of lock used
2. Percent of active connections
3. Heap Memory Utilization %
4. Memory Utilization %
5. Disk Utilization % by Logical Disk

I've looked into Sybase's built-in monitoring tables, but couldn't find these specific metrics there.

Can anyone suggest a way to help me gather this information from multiple Sybase databases? I've searched around but haven't found a clear solution yet.

Any tips or advice would be greatly appreciated! Thanks!

[sladebe]

This is only part of what you're asking for, but it's still a useful command:

[100] MYSERVER.master-09:48:24-1> sp_monitorconfig "all";
Usage information at date and time: Apr  8 2024  9:48AM.

 Name                                Num_free    Num_active  Pct_act Max_Used    Reuse_cnt   Instance_Name
 ----------------------------------- ----------- ----------- ------- ----------- ----------- -------------
 additional network memory              29918198       80906   0.27        80906           0 NULL
 audit queue size                            100           0   0.00            0           0 NULL
 compression info pool size                 4096           0   0.00            0           0 NULL
 disk i/o structures                        4096           0   0.00          129           0 NULL
 heap memory per user                      49138          14   0.03           14           0 NULL
 kernel resource memory                     3746        2650  41.43         2650           0 NULL
 max cis remote connections                  100           0   0.00            0           0 NULL
 max memory                                    0     9437184 100.00      9437184           0 NULL
 max number network listeners                  4           1  20.00            1           0 NULL
 max online engines                            0           1 100.00            1           0 NULL
 memory per worker process                  2044           4   0.20            4           0 NULL
 number of alarms                            385          15   3.75           15           0 NULL
 number of aux scan descriptors              456           0   0.00            0           0 NULL
 number of devices                            21           9  30.00            9           0 NULL
 number of dtx participants                  500           0   0.00            0           0 NULL
 number of java sockets                       40           2   4.76            2           0 NULL
 number of large i/o buffers                   6           0   0.00            1           0 NULL
 number of locks                           98922        1078   1.08         1147           0 NULL
 number of mailboxes                           9          21  70.00           21           0 NULL
 number of messages                           64           0   0.00            0           0 NULL
 number of open databases                     41           9  18.00            9           0 NULL
 number of open indexes                     4990          10   0.20           13           0 NULL
 number of open objects                    14980          20   0.13           20           0 NULL
 number of open partitions                  9990          10   0.10           13           0 NULL
 number of remote connections                 20           0   0.00            1           0 NULL
 number of remote logins                      20           0   0.00            0           0 NULL
 number of remote sites                       10           0   0.00            1           0 NULL
 number of sort buffers                      500           0   0.00           24           0 NULL
 number of user connections                   24           1   4.00            2           0 NULL
 number of worker processes                   20           0   0.00            0           0 NULL
 partition groups                           1024           0   0.00            0           0 NULL
 permission cache entries                     64           0   0.00            0           0 NULL
 procedure cache size                      96544        3456   3.46         4020           0 NULL
 size of global fixed heap                   300           0   0.00            0           0 NULL
 size of process object heap                3000           0   0.00            0           0 NULL
 size of shared class heap                  6144           0   0.00            0           0 NULL
 size of unilib cache                     306184         848   0.28          848           0 NULL
 txn to pss ratio                            400           0   0.00            0           0 NULL

If you see 100% active, and/or non-zero reuse count, that could be a problem (except for max memory and max online engines)

Note that the "permission cache entries" may not be meaningful because of a bug (not sure if that's been fixed)

[HarshPatel1]

Hey Sladebe,

Thanks for the way to have insight of the metrics that I was looking for, but in our use case we want these information from ASE's monitoring tables using remote SQL connection. Do you happen to know if that can be possible or not ?

[sladebe]

Re: we want these information from ASE's monitoring tables using remote SQL connection