Question Re: Troubleshooting Sybase ASE performance issues in Technology Q&A

Troubleshooting Sybase ASE performance issues

MelCalucin — Wed, 23 May 2018 20:19:28 GMT

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.

Re: Troubleshooting Sybase ASE performance issues

M-B — Thu, 24 May 2018 09:16:04 GMT

Hello Mel,

From a database perspective, you have to ask the PaaS provider to start by sending you a sybdiag output from ASE. This is a collection of logs and configs that 'may' give you valuable information about the problem, if the latter writes an error in the ASE log. The sybdiag will also be helpful to check whether ASE needs tuning. Without the provider giving you details about the 'bad query', it'll be difficult to troubleshoot.

The ASE log is the first thing to check. If no errors are seen in the log at the time of the problem, then you need them to send you evidence of the bad query. That will allow you to check the related table.

Here is how to get a sybdiag :

1. Log in as sybase user. On Windows systems, make sure you open the cmd prompt in administrator mode

2. Execute the following on the command line

sybdiag -S<servername> -Usa -T asecore, keyfile, aseadd, osdata

For more details see : https://launchpad.support.sap.com/#/notes/2041802

Hope this helps

Mehrab

Re: Troubleshooting Sybase ASE performance issues

former_member89972 — Fri, 25 May 2018 14:12:07 GMT

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

Re: Troubleshooting Sybase ASE performance issues

MelCalucin — Fri, 25 May 2018 18:04:22 GMT

Avinash,

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

Regards,

Mel

Re: Troubleshooting Sybase ASE performance issues

sladebe — Wed, 30 May 2018 20:16:22 GMT

One easy thing to check is to see if a resource is getting used up. Use:

sp_monitorconfig "all"

If you're looking for a bad query, you can use mon tables, but there are a lot of them. Here's a basic query I use for live monitoring (ie. for currently running queries):

set nocount on
go
set flushmessage on
go
select * into #old from monProcessActivity
waitfor delay "00:00:05"
select * into #new from monProcessActivity
while(1=1)
begin
  select new.SPID, login=suser_name(new.ServerUserID),
         CPUTime=new.CPUTime-old.CPUTime,
         PhysicalReads=new.PhysicalReads-old.PhysicalReads, LogicalReads=new.LogicalReads-old.LogicalReads,
         PhysicalWrites=new.PhysicalWrites-old.PhysicalWrites, new.LocksHeld
         from #old old, #new new
         where old.SPID=new.SPID and old.KPID=new.KPID
         and ( new.CPUTime-old.CPUTime>0 or new.PhysicalReads-old.PhysicalReads>0 or
               new.LogicalReads-old.LogicalReads>0 or new.PhysicalWrites-old.PhysicalWrites>0 )
         order by new.CPUTime-old.CPUTime desc

  truncate table #old
  insert into #old select * from #new
  truncate table #new
  waitfor delay "00:00:05"
  -- Whoops, missed this line.  Added in a later edit:
  insert into #new select * from monProcessActivity
end
go

You can also use monProcessSQLText to see what SQL is running for a given spid (make sure to sort by SPID,BatchID, LineNumber SequenceInLine)

If you want to look at historical data, it's a little more complicated. You can use monCachedProcedures to see accumulated stats for cached procedures (stats go away if the stored proc gets pushed out of cache. There can be multiple cached copies of the same stored proc). There's also monCachedStatement, but you have to have the statement cache enabled for this to work. Cached statements can sometimes have "needs to be recompiled" type side effects (cached statement scans when it shouldn't)

Re: Troubleshooting Sybase ASE performance issues

MelCalucin — Tue, 05 Jun 2018 15:13:00 GMT

This is awesome!

I'll give this a try.

Thanks.

-Mel

Re: Troubleshooting Sybase ASE performance issues

sladebe — Tue, 05 Jun 2018 15:41:20 GMT

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>

Re: Troubleshooting Sybase ASE performance issues

former_member89972 — Tue, 05 Jun 2018 16:17:16 GMT

Mel You have to add :

"insert into #new select * from monProcessActivity"

after

truncate table #new

inside the while loop to make the code Ben work !

You may also want to do "set flushmessage on" to get the outputs out quickly.

HTH Avinash

Re: Troubleshooting Sybase ASE performance issues

sladebe — Tue, 05 Jun 2018 16:32:19 GMT

Ooops. Thanks for the correction.