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)

SAP Adaptive Server Enterprise 16 SP01 New Features

SAP Adaptive Server Enterprise 16 SP01 New Features

http://www.smooth1.co.uk/ase/ase16sp01.html

SAP Sybase ASE “Magic Numbers” and Hardcoded Behaviour

SAP Sybase “Magic Numbers” hardcoded into the product.

Run Queues
0 – System Service tasks
1/2 – unused
3 Group commit,Site handler,Threshold,Returns from lockwait
4 High User
5 Medium User
6 Low User
7 Housekeeper

Tasks attempting to take an exclusive lock are held up by readers with a shared lock
Up to 3 tasks can skip over a queued update task
After a write transaction has been skipped over by 3 tasks or families (parallel queries) that acquire shared locks,
ASE gives a demand lock to the write transaction.
This indicates that a transaction is next in the queue to lock a table, page or row
Subsequent requests for shared locks are queued behind the demand lock

155 bytes per lock

reorg rebuild minimallly logged, requires 120% table size

Before 15.7 certain tasks could not migrate from engine to engine
– disk IO done by engine which issued the IO
– Network IO done by engine assigned at login
– Replication Agent and CIS tasks cannot migrate

Kernel resource memory

– one 2K block per 2 user connections if <=8 engines
– if more engines increase default by 2K for each user connection

Threaded Kernel sp_sysmon

– Average Running Tasks, always 1,5,15 minute moving averages
– CPU Yields “full sleeps”/”interrupted sleeps” high percent “interrupted sleep” increase “idle timeout”
– Thread Utilization, if >70% increase thread count, if thread utilization > engine utilization increase “idle timeout”
as engine not getting enough CPU time
– Page Faults and Context Switches, high counts page faults, involuntary context switches – lack of resources (CPU/memory)
– IO Controllers, DiskControllerActivity/NetControllerActivity equivalent to Disk/Network Checks.
If polls returning max events or events per poll > 3 increase “number of disk/network tasks”
– Blocking Call Activity
If value of queued requests is high compared to serviced requests or wait time is nonzero increase number of threads in syb_blocking_pool

Spinlocks
– Hash tables to reference memory structures
– spinlock/semaphone same in sp_sysmon
– Log Semaphore Contention, if % of total >20% and “ULC Flushed to Xact Log” and “by Full ULC” also >20%, ULC may be too small
– By Full ULC, if >20% consider increasing ULC
– By Change of Database, ULC flushed when modifies object in different database
– By Single Record, by Unpin, by Other if >20% decrease ULC
– When MAX ULC Size = log cache size check flushes bu Full ULC, if >20% consider increasing ULC
– ULC Spinlock Ratio – number of ULC Caches controlled per spinlock

– Cache Turnover, Buffers Grabbed dirty should be 0, else adjust wash marker

Grabbed Dirty not 0 – significant performance problem
– Increase wash size immediate

sp_logiosize and large IO pool
use 8 if not sure
stored in sysattributes for the database

sp_countmetadata – Number of objects of type and memory needed, add 5% to open objects and 10% to open indexes for temporary tables

Minimum size of a procedure in cache is 2K
3K procedure uses 2x2K pages

Statement cache

– Each cached statement uses 1 object descriptors “number of open objects”

– Each query plan generated and used by the statment cache uses at least 2x2K pages
512-1024 for sql text
– 1 page per copy of the plan

Only 1 user may reference a particular copy of a statement cache plan

Semantic Partitioning

Range partitioning up to 31 partition keys

List partitioning
– only 1 column for the key
– Boundary values must be compatibile with partition key datatype
– Implict conversion will take place for different but compatibile data types
– Transaction will fail if implict conversion results in data loss
– Insert value not part of list will fail
– List can have up to 250 values
– Value list must be unique across partitions

sp_configure “number of open partitions” > “number of open objects” + “number of open indexes”

Histogram tuning factor
– default 20 steps
– default histogram tuning factor 20
– 400 steps

ASE does not consider the number of CPUs or disks
ASE relies on degree of parallelism being configured correctly

Parallel Access

– Lock Promotion occurs per worker hence the family can have (n-1)*threshold for promotion

If cache hit ratio >90% partitioning table not greatly improve performance

Partition skew = largest partition/partition average, rebalance if >1.3.
If >2 optimizer will not choose a parallel plan

For single processor machines set “runnable process search count” to 2/3  For 15.7 thread idle timeout

sp_configure ‘additional network memory” – for additional memory used for network packets which are larger than default size

Clients cannot use larger packet sizes if “max network packet size” is increased but “additional network memory” is not increased

Sizing Additional Network Memory
– Estimate number of users requesting large packet sizes
– Estimate the sizes the application will request
– Multiple by 3
– Add 2% for overhead
– Round to multiple to 2048

sysprocesses.network_pktsz

MRU replacement strategy used when
– optimizer estimates >50% of the buffers in cache will need to be displaced

Relaxed LRU may not be worth considering with <=4 CPUS.

APF defaults to 10% of given buffer pool – if async prefetched pages fill 10% of buffer pool APFs denied until
amount of pending buffers has been reduced