A drop in SAP ASE performance is seen when a statement cache has high turnover.
- 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
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.
1> dbcc prsqlcache
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
About this pageThis 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.