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
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 for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.