Spotlight on SAP ASE 2.12 - User Guide

Procedure Cache Reads Alarm

This alarm is raised when the rate of physical reads requested by the procedure cache (expressed as reads per second) exceeds normal levels.

Since this metric is included in calibration, the maximum value, and thereby the alarm thresholds and flow speed, are determined by statistical sampling.

This rate is determined by cache searches that did not find the needed stored procedure execution plan in the procedure cache (a cache miss) and had to do a perform physical I/O to read in the stored procedure and place a new plan in memory.

Note: This will have a large (and negative) impact on the overall performance of the Adaptive Server.

When a server first starts, the number of reads may be higher than normal until the most utilized stored procedures have been executed and loaded into cache.

See sp_configure "procedure cache size", "<new size in 2K pages>" to increase the size of the cache.

Use the Memory drilldown | Procedure Cache Page to find out detailed cache utilization information.

Procedure Cache Searches Alarm

This alarm is raised when the rate of searches requested from the procedure cache (expressed as searches per second) exceeds normal levels.

Since this metric is included in calibration, the maximum value, and thereby the alarm thresholds and flow speed, are determined by statistical sampling.

This rate includes searches that resulted in the needed stored procedure being found in cache (a cache hit) and those searches that required a disk read (a cache miss).

Cache searches are one indication of server activity.

Use this data, along with physical reads, to determine how effective the overall cache size for this server is.

Use the Memory drilldown | Procedure Cache Page to find more detailed performance metrics for the procedure cache. Use the grid to see what stored procedures (or triggers) are currently loaded in the procedure cache.

Response Time Alarm

This alarm is raised when the amount of time a sample (or benchmark) SQL statement took to run exceeds normal thresholds.

Since this metric is included in calibration, the maximum value, and thereby the alarm thresholds, are determined by statistical sampling.

If a query has been changed, or the performance expectations change, run calibration again for this Adaptive Server during a time frame representative of a typical workload.

The SQL statement packaged by Spotlight can be customized to reflect a more representative transaction.

To customize the SQL statement

1. Select View | Options | Spotlight on SAP ASE | Acquisition.
2. Edit the SQL statement as needed.

Caution: In this release of Spotlight, all SQL is executed through the same connection to the ASE. If a long running SQL statement is inputted for the Benchmark Query, all queries from Spotlight will be impacted.

Glossary