Troubleshooting Sybase ASE performance issues

[MelCalucin]

I'm new to Sybase ASE, but I had been a DBA for MS SQL Server in previous jobs. I'm now one of the development managers for SAP Data Services which using Sybase ASE. Note, I'm not the basis person for Data Services. The Data Services servers and corresponding Sybase ASE server are hosted by a PaaS provider.

At times, Data Services becomes inaccessible. I can't get to Data Services CMC or the Data Services Management Console.

The PaaS provider is telling us a bad query is impacting the Sybase ASE server. I've asked them what database and what tables are being queried, but I never get an answer from them. I know I can determine this from MS SQL Server, but is it possible to identify what query is causing the issue in Sybase ASE? If so, what is the process of determining this?

Thanks.

[former_member89972 · Accepted solution]

Welcome to SAP/Sybase ASE world !

MSSQL Server inherited a lot from former SQLServer now know as ASE. So the grounds should be familiar.

ASE has a few tools to help DBAs

- sysmon for overall server health

- MDA tables to narrow down to trouble makers.

- large number of out the box system procedures to help

- usual tools to know health of with server errorlog, sybdiag for health of indexes, tables, dbcc, etc

So start familiarizing with those, especially the MDA tables.

For a price, there are also third party tools like DPA from Solarwinds, Surveillance from Bradmark, etc.to help you.

HTH and Good luck

Avinash

.

[MelCalucin · Accepted solution]

Avinash,

Thanks! This is exactly what I'm looking for.

Regards,

Mel

[sladebe · Accepted solution]

Re: sysmon for overall server health

One thing to watch for in sysmon: in the "Kernel Utilization" section, the "I/O Busy" percentages can be erroneously high when running ASE in the new threaded mode. From Knowledge Base Article 1863319:

* The I/O Busy count does not have the same meaning in threaded mode as it does in process mode. The reason being is that engines are not performing I/Os in threaded mode. Other designated threads (not engines) are performing I/Os in threaded mode.  

* Currently, when in threaded mode, engines are counted as "I/O Busy" if ASE has any I/O outstanding and the engine is idle. If there is one I/O outstanding and three engines sitting idle, each engine is counted as "I/O Busy". 

* The issue has been identified and logged under SAP CR 757246. Its description reads, "sp_sysmon IO Busy is over weighted in threaded kernel mode.".

* Resolution - There is currently no target SAP ASE Service Pack scheduled for this issue (SAP CR 757246). It is being reviewed by our Product Group. Please mark this KBA as a Favorite to be automatically notified when the CR is fixed.<br>