Skip to Content

SAP ASE on BW: monSpinlockActivity shows high contention



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.

Kazuo Otani

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

0 Answers