SAP ASE on BW: monSpinlockActivity shows high contention

[kazuootani]

sp-sysmon-with-cpu-full.txt

Hi,

I am looking at the sp_sysmon log from one of my customer who is running SAP BW on SAP ASE.

SAP ASE 15.7 SP133
Windows 2008 R2

The ASE and NetWeaver 7.3 are running on a virtual Windows environment.
No other CPU/memory consuming application is running.

Some of configuration parameters:

"max memory" = 26624MB
"procedure cache size" = 6251MB
"statement cache size" = 400MB
"max online engines" = 5
"global cache partition number" = 8
"number of open objects" = 60000
"number of open indexes" = 60000
"number of open partitions" = 50000
"default data cache" = 13517MB
"log cache" = 512MB

The result of sp_sysmon shows the highest CPU usage. All engines are 100% busy.

Usually, higher CPU percentage is caused by spinlock contention at the most used data cache. But it is not significantly high, just 5.3% in sp_sysmon.

However, the result of monSpinlockActivity shows very high contention in "default data cache".

1> select SpinlockName,Grabs,Spins,Waits,Contention from monSpinlockActivity
2> where SpinlockName = "deafult data cacahe"
3> go
SpinlockName        Grabs        Spins        Waits       Contention ------------------  -----------  -----------  -----------  ---------- default data cache  35970570877  20988369922  35557783116  0.988524

I am thinking of two actions to improve this situation:

1. Increase "global cache partition number"
2. Bind most accessed tables to different named caches

"global cache partition number" is already set to 8 for 5 engines.
Does it make sense to raise "global cache partition number" to 16 or more?

Any suggestions for reducing high spinlock contention?

So far, from sp_sysmon log, I found
- Insufficient "number of open partitions"
- Insufficient "statement cache size"
But I don't think they are the primary reasons of high CPU percentage.

I will provide additional information if you need.

Regards,
Kazuo Otani