SAP Adaptive Server Spinlinks Masterclass with Raymond Ho

ASE Spinlocks

– 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).

sp__tsnap topspins,help

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!

Spinlock waits

– 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)

Advertisement

---