2600140 - Slow performance, high engine utilization, error 701 and spinlock contention on the statement cache -SAP ASE

A drop in SAP ASE performance is seen when a statement cache has high turnover.

Symptoms include

• High engine utilization (97-100%)
• Statements are no longer found or cached into the statement cache
• Error 701 State 12 is returned to the client
• spinlock contention on the statement cache spinlock (SSQLCACHE_SPIN)

Indicators to determine the ASE is impacted by this problem:

• sp_sysmon reports only Statements Not Found, all other Statement metrics are 0
• Example:

Statements Cached                 0.0           0.0           0       n/a
Statements Found in Cache         0.0           0.0           0       n/a
Statements Not Found             97.8           3.2       29334       n/a
Statements Dropped                0.0           0.0           0       n/a
Statements Restored               0.0           0.0           0       n/a
Statements Not Cached             0.0           0.0           0       n/a

• A select from monCachedStatement produces 0 rows
• output of dbcc prsqlcache shows no cached statements despite all memory is used along with total Memory used is greater than Memory configured.
• Example:

1> dbcc prsqlcache
2> go

Start of SSQL Hash Table at 0x0x153863850

Memory configured: 100000 2k pages Memory used: 114348 2k pages

Total Statement Ct: 0, FREE chain is empty!
Empty Bucket Ct: 8191, 0 buckets with max. entries of: 0, Avg. Entries Per Bucket: 0.000000, Std. Dev.: 0.000000

• SAP Adaptive Server Enterprise (ASE) SP03 PLx only (15.7 and 16.0 SP01 and SP02 are not affected)
• statement cache is enabled

CR813266, CR#813266, 813266, Error 701, State 12, statement cache , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Problem