cancel
Showing results for 
Search instead for 
Did you mean: 

ASE Tuning - How to find the bottleneck?

Private_Member_19084
Active Contributor
0 Kudos

Hi experts,

we have a lot of SAP systems running on ASE.

How can I find out, what is the bottleneck in the ASE?

I know there are a lot of parameters and possibilities to increase (memory, caches, disc-structure, ...)

But I want to do it effective and so 1st I want to find out, what is the bottleneck?

My 1st idea would be the saptempdb, as I have one slow system on which the saptempdb is very small.

The saptempdb is used by 100%, for sure, but how do I find out now, if this is a bottleneck or not?

ASE Data cache is by around 99,5% btw.

I've already found about saptempdb, that it is used for sorting, indexing, .... but still don't see how big it should be and how it should be concepted.

Kind regards

Accepted Solutions (0)

Answers (4)

Answers (4)

bart_van_kuijk
Participant

SAP Notes and KBAs are only available when having a maintenance (Support) contract with SAP.

hans-juergen_schwindke
Active Participant
0 Kudos

Hi,

I don't know which tool(s) you're using and whether they have any monitoring capabilities. So here are some general remarks:

Your data cache has a filling level of 99,5%. This is not an issue as caches are intended to hold data. Data is read from disk and put into data cache. And if your system is running for some time your cache will become full. If more data is read from disk, some old data is removed from cache. (Old data means data that has not been touched for a long time).

So from the pure percentage you cannot derive that your cache is a bottleneck. Important is the cache turnover; so if your cache content is replaced frequently this might be a bottleneck.

From your description I would guess that saptempdb could be a bottleneck. If you have some space you can increase it.

For a more detailed and substantial analysis you require a monitoring.

As a simple (to execute) approach I like to start with is sp_sysmon monitoring. This runs for a certain time and collects a lot system information but it is not easy to read and understand. Here is a link to the documentation:

Performance and Tuning Series: Monitoring with sp_sysmon - SAP Library

Maybe you can run it once (during a period of high system load) and post the output into this scn thread.

sp_sysmon writes the results to your sql console. The run-time is given in the format 'hh:mm:ss'. So a data capture for three minutes looks like this:

sp_sysmon '00:03:00'

go

Some counters in sp_sysmon are not resetted so that it does not influence other monitoring. If you don't have other monitoring or if it does not matter to reset counters, you can run sysmon with the clear option:

sp_sysmon '00:03:00', clear

go

Best regards,

Juergen

Johan_sapbasis
Active Contributor
0 Kudos

Hi,

Another thing check these notes.

1722359 SYB: Running SAP applications on SAP ASE - Best Practice

1680803 SYB: Migration to SAP Adaptive Server Enterprise - Best Practice

2049506 SYB: Tables with special optimizer treatment

1898535 SYB: SAP Sybase ASE service pack update compatibility

1752266 SYB: How to create an additional tempdb with Sybase ASE

1702338 SYB: Database hints in Open SQL for Sybase ASE

1634716 SYB: Lock timeout or deadlocks

1646155 SYB: Configuring auxiliary scan descriptors

1724091 SYB: Performance of VBDATA and other VB/RFC tables

Kind Regards,

Johan

darshinee
Explorer
0 Kudos

Hi Johan,
These Notes are not accessible to me. Are they publically available? Really need them for the customers like us.
Regards,
Punya

Johan_sapbasis
Active Contributor
0 Kudos

Hi,

What is the version of SAP, Sybase and OS you are running.

have your processed sp_monitorconfig in isql yet?

Regards,

Johan