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

SAP Knowledge Base Article - Preview

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

Symptom

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


Read more...

Environment

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

Product

SAP Adaptive Server Enterprise 16.0

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.