Spotlight on SAP ASE 2.12 - User Guide

Locks Waiting Alarm

This alarm is raised when the number of locks waiting to be granted is above normal levels. Since this metric is included in calibration, the maximum value, and thereby the alarm thresholds, are determined by statistical sampling.

Any time a lock waits for another process to complete its transaction and release its locks, the overall response time and throughput for an application are affected.

Often just an increase in the number of simultaneous users of a server may be contributing to an increase in lock contention.

A consistent trend increase warrants a detailed investigation of the locking strategy employed on the Adaptive Server. You can research locking configuration and tuning in SAP Performance and Tuning Guide for ways to decrease lock contention. Common suggestions include creating indexes (particularly on tables that have many updates and deletes) and looking for ways to keep transactions short.

Go to the Locks drilldown for specific information about the locks within an Adaptive Server.

Go to the User Activity drilldown to see detailed information about user security.

Open Databases Percent Alarm

This alarm is raised when the percentage of open databases for the server (based on the number available) increases above the ranges defined by the thresholds on this component.

Keeping a server from running out of metadata descriptors is important. Performance on the server is improved by allowing the server to cache needed object information in memory instead of doing expensive disk I/O. In addition, it reduces synchronization and spinlock contention when Adaptive Server has to retrieve database, index, or object information at runtime.

To determine the optimal number of open databases for your environment

1. Find the maximum number of active databases. Monitor this value in Spotlight during a peak period.

2. Reset the number of open databases to that number plus 10 percent.

3. Periodically review this setting during time of high activity.

You configure the number of open databases using:

sp_configure "number of open databases," "<new number to configure>"

For memory impact on the increase, see the Memory drilldown | Configuration page.

Select the respective configuration variable name and enter a speculative value to which you want to increase the configuration.

Open Indexes Percent Alarm

This alarm is raised when the percentage of open indexes for the server (based on the number available) increases above the ranges defined by the thresholds on this component.

Keeping a server from running out of metadata descriptors is important. Performance on the server is improved by allowing the server to cache needed object information in memory instead of doing expensive disk I/O. In addition, it reduces synchronization and spinlock contention when Adaptive Server has to retrieve database, index, or object information at runtime.

To determine the optimal number of open indexes for your environment

1. Find the maximum number of active indexes. Monitor this value in Spotlight during a peak period (see the respective graph in the Memory drilldown | Metadata Cache page).

2. Reset the number of open indexes to that number, plus 10 percent.

3. Periodically review this setting during times of high activity.

You configure the number of open indexes using:

sp_configure "number of open indexes", "<new number to configure>"

For the memory impact of the increase, see the Memory drilldown | Configuration page.

Select the respective configuration variable name and enter a speculative value to which you want to increase the configuration.

Open Objects Percent Alarm

This alarm is raised when the percentage of open objects for the server (based on the number available) increases above the ranges defined by the thresholds on this component.

Keeping a server from running out of metadata descriptors is important. Performance on the server is improved by allowing the server to cache needed object information in memory instead of doing expensive disk I/O. In addition, it reduces synchronization and spinlock contention when Adaptive Server has to retrieve database, index, or object information at runtime.

To determine the optimal number of open objects for your environment

1. Find the maximum number of active objects. Monitor this value in Spotlight during a peak period (see the respective graph in the Memory drilldown | Metadata Cache page).

2. Reset the number of open objects to that number, plus 10 percent.

3. Periodically review this setting during times of high activity.

You configure the number of open objects using:

sp_configure "number of open objects", "<new number to configure>"

For the memory impact of the increase, see the Memory drilldown | Configuration page.

Select the respective configuration variable name and enter a speculative value to which you want to increase the configuration.