cancel
Showing results for 
Search instead for 
Did you mean: 

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

HarshPatel1
Explorer
0 Kudos

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!

View Entire Topic
Mark_A_Parsons
Contributor
0 Kudos

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

What do you mean by "using remote SQL connection"?

  • Are you connecting from an application into ASE?
  • Are you sitting in a database running a query against a remote/proxy/linked/virtual table that points at ASE?
  • Do you have the ability to invoke/run a (ASE) stored proc over your SQL connection?
  • Can you submitt DDL (eg, create table, drop table) across this SQL connection?

FWIW, sp_monitorconfig (see Ben's answer) has the ability to write its output to a table, after which you could query said table. I can think of a couple ways to implement this but they would require the ability to invoke/run a stored proc over your SQL connection.

------------------

Another (albeit convoluted) option would be to create a proxy table (in ASE) that acts as a 'front end' for executing sp_monitorconfig.  You would then query said proxy table over your SQL connection.

One approach to setting up a proxy table in ASE:

 

-- all of the following is performed in the ASE instance where you
-- wish to run sp_monitorconfig

-- if not already defined then create a 'loopback' server:

declare @servernetname varchar(30)

select @servernetname = srvnetname
from master..sysservers
where srvname = @@servername

exec sp_addserver loopback, NULL, @servernetname
go

-- replace the following with the name of your ASE database where
-- you wish to create the wrapper proc and proxy table

use tempdb
go

-- create wrapper proc to call sp_monitorconfig 'all':

create proc mc_proc
as
exec sp_monitorconfig 'all'
go

-- create proxy table to act as 'front end' to proc 'mc_proc':

create existing table mc_proxy
(Name varchar(35)
,Num_free int
,Num_active int
,Pct_act char(6)
,Max_Used int
,Reuse_cnt int
,Instance_Name varchar(30)
)
external procedure at 'loopback.tempdb..mc_proc'
go

 

Now to compare sp_monitorconfig vs mc_proxy:

 

1> sp_monitorconfig 'all'
2> go | head
Usage information at date and time: Apr 10 2024 4:29PM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
----------------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
additional network memory 32874 65430 66.56 65430 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 2000 0 0.00 19 0 NULL
heap memory per user 4085 11 0.27 15 0 NULL
kernel resource memory 4147 2249 35.16 2249 0 NULL


1> select * from mc_proxy
2> go | head
Usage information at date and time: Apr 10 2024 4:29PM.

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
----------------------------------- ----------- ----------- ------- ----------- ----------- ------------------------------
additional network memory 32874 65430 66.56 65430 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 2000 0 0.00 19 0 NULL
heap memory per user 4082 14 0.34 15 0 NULL
kernel resource memory 4147 2249 35.16 2249 0 NULL

 

NOTE: both sets of output show up in nicely formatted columns (just like in Ben's answer) but for some reason I'm having problems getting this pos editor to accept the pretty format

At this point you would query the table tempdb..mc_proxy over your SQL connection.

One potential issue is the textual header - "Usage information at date and time: Apr 10 2024 4:29PM."  - that is generated as a result of running sp_monitorconfig.

This may or may not be an issue depending on how your application/query-tool processes the info coming back across your SQL connection.

One hack to get rid of the unwanted textual header ... modify sp_monitorconfig to disable the removal of the textual header.

--------------------

re: 5. Disk Utilization % by Logical Disk

Consider:

  • sp_helpdevice (though you'll need to parse the contents of the description 'column')
  • reverse engineer the query(s) from sp_helpdevice that generate the desired data
  • a join between master..sysdevices and master..sysusages
  • a join between master..monDeviceSpaceUsage and master..monDeviceSegmentUsage
HarshPatel1
Explorer
0 Kudos

Hey, @Mark_A_Parsons, Thankyou for your response. In our architecture, we utilize a Python script leveraging the PyODBC library to extract server, processing, and network statistics from monitoring tables(https://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc36274.1600/doc/html/san139305249522...). Utilizing procedures like sp_monitorconfig is not feasible with this python script to fetch the necessary data. Therefore, we are forced to fetch the required information directly from the system monitoring tables in Sybase. And hence the question remains the same, we need to fetch the above mention data points directly from the Sybase Monitoring tables, by strictly using this python script. So, can you provide me an solution to this problem without changing our python architecture?

Required data points:

  1. Percent of lock used
  2. Percent of active connections
  3. Heap Memory Utilization %
  4. Memory Utilization %
  5. Disk Utilization % by Logical Disk
sladebe
Active Participant
0 Kudos

Re: can't run sp_monitorconfig "all" from a Python client

It seems doable for me:

 

#!/usr/bin/env python3

# python_path_for_sybase="<rootdir>/sybase/python/python37_64r/lib"
# import sys
# sys.path.append(python_path_for_sybase)

# See https://help.sap.com/viewer/a1576559612d4e39886fc0ad4e093074/16.0.3.5/en-US/b07470dfbbf91014b04dcfac955e3acc.html
import sybpydb

# Login needs mon_role and sa_role
login=input("enter login:")
password=input(f"Enter password for {login}:")
# The docs say ChainXacts=false defaults to false, but that's not true.
db = sybpydb.connect(servername='MYSERVER', user=login, password=password, dsn='Timeout=10;LoginTimeout=10;PacketSize=2048;ScriptName=test_syb.py;ChainXacts=false')
c = db.cursor()
c.execute("sp_monitorconfig 'all'")
result_rows=c.fetchall()
for row in result_rows:
  print(row)

 

 

HarshPatel1
Explorer
0 Kudos

Hey, @sladebe, again thanks for you response, I had checked that sybpydb python library does not support python version greater than python 3.7. And in our architecture we strictly need to run our python script in python version greater than 3.9. Due to this limitation we are using pyodbc library in python. So, in our architecure we can't run sp_monitorconfig 'all' procedure.

While the sp_monitorconfig 'all' procedure, we are getting the follow error:
pyodbc.Error: ('HY000', "[HY000] [Devart][ODBC][Adaptive Server Enterprise]Can't run sp_monitorconfig from within a transaction. (0) (SQLExecDirectW)")

So, can you provide me an solution to this problem without changing our python architecture?

sladebe
Active Participant
0 Kudos

The version of the Python interpreter I was running was 3.9

Re: Can't run sp_monitorconfig from within a transaction

I think that means you have autocommit set to True (automatically run "begin transaction" before each SQL statement, run "commit transaction" after each SQL statement).   As the error message says, that doesn't work with sp_monitorconfig.  Try running it with autocommit set to False (connect with autocommit set to False):

https://stackoverflow.com/questions/33713800/pyodbc-autocommit-does-not-appear-to-work-with-sybase-a...

HarshPatel1
Explorer
0 Kudos

Hey, @Mark_A_Parsons, with the help of your response we are able to fetch the data form sp_monitorconfig within the python script. But we still has some doubt regarding the initial data points that we need. The questions are as follow:

1. In sp_monitorconfig 'all' we need the data point for a particular instance id. Is their any way to fetch the data by instance id. Also, the data points in sp_monitorconfig are for a particular sybase server or instance or cluster? (What is the difference between sybase server and instance. I could not found the proper answer in the documents, maybe you can explain it better.)

2. Does 'max memory' resource in sp_monitorconfig shows the current usage of memory?, because in the sybase document I found that max memory specifies the maximum amount of total physical memory that you can configure SAP ASE to allocate. (Reference Link: https://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc31654.1600/doc/html/san1...)

3. For Heap memory, the sp_monitorconfig is providing the heap memory per user, but we need total heap memory utlization percentage, is it the same? I could not found more details in the sybase document.

Thankyou for your assistance.