SQL Server %%physloc%%


SELECT*,%%physloc%% ASphysloc
  FROMdbo.tst AST
SELECT*,sys.fn_PhysLocFormatter(%%physloc%%) ASPLF
  FROMdbo.tst AST
SELECT * FROM dbo.tst AS T
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) AS FPLC
ORDER BY FPLC.file_id, FPLC.page_id, FPLC.slot_id;

Sybase ASE 16.0 default values

Sybase ASE 16.0 default values

echo ‘sp_configure
go’ | ./isql_ASE16.sh | gawk ‘{ print substr($0,1,61) ” ” substr($0,63,22) ” ” substr($0,200,20)}’


What’s New in SQL Server 2016 BI


What’s New in SQL Server 2016 BI


What new in Master Data Services and Integration Services in SQL Server 2016


Microsoft Ignite 2015 – What’s New items

SQL Server 2012 AlwaysOn Availability Groups corruption issue


FIX: Corruption occurs on pages of secondary replica when you change the secondary replica to unreadable

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


SAP ASE 16 New Features

SAP ASE 16 New Features – http://www.smooth1.co.uk/ase/ase16.html

SAP® Adaptive Server® Enterprise 16 New Features Overview – http://www.sql-ag.de/uploads/media/SAP_Sybase_ASE_16_NewFeaturesOverview.pdf