SAP Adaptive Server Spinlinks Masterclass with Raymond HoPosted: May 20, 2015
– user log cache spinlock ratio
– open object spinlock ratio
– lock table spinkock ratio
– data cache partitions
No spinlocks for IO pools or if there is only 1 engine
Access to a hash table is serialized by a spin lock.
Mechanism for spinning is platform specific, some platforms loop on NOP instruction.
– CPINFOMEM_SPIN Resource->rprocpgr_spin
– SSQLCACHE_SPIN Resource-> # New spinlocks
– Pdes spinlock # Older spinlocks
– Sched Q
– default data cache
Caches work on dbid/logical page #
Hash table automatically sized depending on resource pool size
Each cell is a linked list.
Pointer holds buffer ID and page ID
LIsts grow and shrink depending upon buffer allocation and deallocation
Average chain length is visible in sp_sysmon
Select Query – ASE hashs page ID (dbID + logical page #) to a cell number
Buffers reads – update used count for the buffer so buffers are not removed from cache
sp_sysmon does not show all spinlocks
sp_sysmon Spinlock Contentsions are Wait/Grab percentage not relative overhead of different spinlocks and number of spins.
sysmonitors Table http://scn.sap.com/docs/DCC-
master..monSpinLockActivity 15.7 ESD#2 – total since last server restart
100 named spinlocks – Spins(1 or a series of spins? overhead count not be 1, may be platform specific),Grabs (1 grab),Waits(1 wait).
Procedure cache – 1 spinlock, increase size
Reduce spinlock ratio 100:1 to 10:1
Increase cache partitions higher than number of engines?
Buffers can move between partitions!
– short waits
– SPID uses 1 spinlock per page retrieved, may need to access may partition spinlocks
Logical Lock Waits
– in sleep queue (long waits)
MASS (Memory Address Space Segment) waits on AIOs due to checkpoint,housekeepers
– waiting in sleep queue (medium to long waits)
– 1 MASS bit protects 1-8 (IO pool size) buffers during IO
Proc cache size
– 1 spinlock for proc cache buffers, there are others proc cache related
– ELC 15.7 GA 50%, 15.7 SP100 20%
– Each engine as comes online is pre-allocated equal slice of ELC
– ELCs can expand from global block pool aka Global Cache (GC)
– dbcc proc_cache(print_elc)
– proc cache – 1 spinlock for updating allocation bitmap pages
– Statement cache up to it’s limits can steal from proc cache (Global Cache+ELC’s)
Turn on TF753, leave TFx off TF758
If using statement cache turn off “streamlined dynamic SQL” and “enable plan sharing”
CR#744179 turn off plan sharing
CR#760876 turn off streamlined dynamic SQL
Use TF7738 – Support plan sharing of cached statements across different user logins
cache statement spinlock – SSQLCACHE_SPIN
If dynamic SQL rather than prepared statements – spinlock Resource->rdesmgr_spin (Open Object Descriptors)