The purpose of this page is to clarify the understanding of how SAP ASE uses spinlocks and what the effects on overall CPU usage may be.
Often high CPU in SAP ASE can be traced to spinlock usage. This page will show how to identify that condition and suggest ways to tune ASE.
What is a Spinlock?
In a multi-engine server synchronization mechanisms are needed to protect shared resources
ASE uses spinlocks as one of its synchronization mechanisms
A spinlock is a data structure with a field that can only be updated atomically (that is, only one engine at a time can make changes to it).
When a task modifies a data item which is shared it must first hold a spinlock
Shared items are such things as run queues, data cache page lists, lock structures, etc.
The spinlock prevents another task from modifying the value at the same time
This of course assumes that the other task also performs its access under the protection of a spinlock
A task needing a spinlock will “spin” (block) until the lock is granted
When multiple engines are spinning at the same time CPU usage can rise substantially.
Spinlocks must be as fast and efficient as possible
In order to reduce contention a process which loops typically acquires and releases its spinlock each time through the loop.
Therefore the spinlock code is written in platform-specific assembly language.
Comparison of Spinlocks to Other Synchronization Mechanisms
Should be small
Can vary considerably
Spinlocks and CPU Usage
Spids trying to get a spinlock will never yield the engine until they have it.
So one spid, waiting on a spinlock, will cause 100% user busy on one engine until it gets the spinlock.
Spinlock contention percentage is measured as waits/grabs
Example: 10,000 grabs with 3,000 waits = 30% contention
For looking at performance issues, use total spins, not contention
Example: Assume two spinlocks
One had 100 grabs with 40 waits and 200 spins = 40% contention
Second had 100,000 grabs with 400 waits and 20,000 spins = 4% contention
The second used up more many cpu cycles spinning, even though contention was lower.
We should then look at tuning for the second example, not the first.
As more engines spin on the same spinlock, the wait time and number of spins increases; sometimes geometrically
Spinlock contention/spinning is one of the major causes of high CPU
Step 1 is determining if, in fact, the high cpu is being caused by spinlock usage.
Step 2 is determining which spinlock or spinlocks are causing the condition.
Step 3 is determining what tuning to use to help reduce the problem.
*Note* You will never get to 0% spinlock contention unless you only run with one engine. That is, do not think that spinlock contention can be eliminated. It can only possibly be reduced.
Step 1 - Checking for spinlock contention/spinning
Using sp_sysmon to determine if high cpu is due to spinlocks
Check “CPU Busy” (or “User Busy” in 15.7 Threaded Mode).
If engines are not showing high busy% then spinlocks are not a big issue.
Check “Total Cache Hits” in the “Data Cache Management” section.
If the cache hits per second is high, and goes up with cpu busy %, then you likely are looking at table scanning/query plans and not spinlocks.
In general, if cpu usage increases but measurements of throughput such as committed xacts, cache hits, lock requests, scans, etc. go down then it is very possible that spinlock usage is an issue
Step 2 - which spinlock or spinlocks are causing the contention?
There are several spinlocks listed, but only contention % is shown
Object Manager Spinlock Contention
Object Spinlock Contention
Index Spinlock Contention
Index Hash Spinlock Contention
Partition Spinlock Contention
Partition Hash Spinlock Contention
Lock Hashtables Spinlock Contention
Data Caches Spinlock Contention
High contention on any of these may indicate a problem
But, you may have contention on other spinlocks not reported in sp_sysmon
Using MDA table monSpinockActivity
This table was added in 15.7 ESD#2
Query using standard SQL.
One possible query showing the top 10 spinlocks by number of spins over a one-minute interval
select * into #t1 from monSpinlockActivity
waitfor delay "00:01:00"
select * into #t2 from monSpinlockActivity
select top 10 convert(char(30),a.SpinlockName) as SpinlockName,
(b.Grabs - a.Grabs) as Grabs, (b.Spins - a.Spins) as Spins,
(b.Waits – a.Waits) as Waits,
case when a.Grabs = b.Grabs then 0.00 else convert (numeric(5,2),(100.0 * (b.Waits - a.Waits))/(b.Grabs - a.Grabs))
end as Contention
from #t1 a, #t2 b where a.SpinlockName = b.SpinlockName
order by 3 desc
Possible Issues with monSpinlockActivity
Spinlocks with multiple instances will get aggregated
For example, all default data cache partition spinlocks will show up as one line
This can make it impossible to see if just one cache partition is causing the problem
You must set the 'enable spinlock monitoring' configuration variable
Tests show that this adds about a 1 percent overhead to a busy server.
monSpinlockActivity does show the current and last owner KPIDs. This can be useful to check if certain processes are the ones heavily hitting certain spinlocks.
Step 3 - what tuning to can be done to help reduce the problem
This is going to depend a great deal on which spinlock(s) the high spins are on.
Note as well that it is quite possible to reduce contention on one spinlock only to have it increase on another
Some of the more common spinlocks and possible remedies
Object Manager Spinlock (Resource->rdesmgr_spin)
Make sure that sufficient ‘number of open objects’ have been configured.
Identify ‘hot’ objects by using monOpenObjectActivity. The Operations columns counts number of access (open) of a table.
Use dbcc tune (des_bind) to bind the hot objects to the DES cache.
The reason this works is that the spinlock is used to protect the DES keep count in order to make sure an in-use DES does not get scavenged. When the DES is bound that whole process gets skipped.
Data Cache spinlocks
The best single method to reduce data cache spinlock usage is to increae the number of partitions in the data cache.
Note that if a cache can be set to ‘relaxed LRU’ the spinlock usage may be decreased dramatically. This is because the relaxed LRU cache does not maintain the LRU->MRU chain, and so does not need to grab the spinlock to move pages to the MRU side.
There are definite requirements for this to help (a cache that has high turnover is a very poor candidate for relaxed LRU).
Procedure Cache Spinlock (Resource->rproccache_spin)
This spinlock is used when allocating or freeing pages from the global procedure cache memory pool (this includes statement cache).
Some possible causes include
Proc cache too small – procs and statements being frequently removed/replaced.
Large scale allocations
To reduce pressure on the spinlock
Eliminate the cause(s) for procedure recompilations (maybe TF 299)
If you are running a version prior to ASE 15.7 ESD#4 upgrade. ASE 15.7 ESD#4 and 4.2 have some fixes to hold the spinlock for less time
Trace flags 753 and 757 can help reduce large-scale allocations
In ASE versions past 15.7 SP100, use the configuration option "enable large chunk elc“.
Use dbcc proc_cache(free_unused) as temporary help to reduce spinlock/cpu usage.
Procedure Cache Manager Spinlock (Resource->rprocmgr_spin)
This spinlock is used whenever moving procedures and dynamic SQL into or out of procedure cache.
This spinlock was also used prior to ASE 15.7 ESD#1 when updating the memory accounting structures (pmctrl).
Due to contention a separate spinlock was created.
Causes of high contention include:
Heavy use of dynamic SQL
Procedure cache sized too small
Possible remedies are the same as for rproccache_spin
Lock Manager spinlocks (fglockspins , addrlockspins, tablockspins)
These spinlocks are used to protect the lock manager hashtables.
If the lock HWMs are set too high, that means more locks and more contention
Configuration tunables are the primary way to address this
lock spinlock ratio
lock address spinlock ratio
lock table spinlock ratio
lock hashtable size
What not to do
Resist the urge to add more engines because cpu is high
Adding additional engines when the high cpu busy is caused by spinlock contention will only make matter worse
Adding more "spinners" will simply increase the amount of time it takes each spid to obtain the spinlock, slowing things doen even more.