References > Template Reference > Sybase > Sybase ASE

Sybase ASE

This template assesses the performance of the Sybase ASE database by retrieving performance data from the monitoring tables.

Prerequisites

Sybase ASE 15.0. Sybase ASE ODBC must be installed on the SAM server. (Instructions can be found on the Sybase ASE installation media.) Complete the following before using this template:

  1. Database user must have mon_role role.
  2. The database server should have the following options enabled:
    • Enable monitoring
    • Wait event timing
    • Object lockwait timing
    • Statement statistic active
    • Per object statistic active
    • Statement pipe active
    • Statement pipe max messages
    • SQL batch capture
    • SQL text pipe active
    • SQL text pipe max messages

    More information on how to enable these options can be found here: http://www.sypron.nl/mda.html.

  3. The database server and the ODBC driver on the SAM server should use the same character set.
    If you receive the following error: "Could not load code page for requested charset," inspect what character set is used by the client by default by looking in SYBASE_PATH\locales\locales.dat. Also, look at which character set is used by the database server.
    More information about character sets can be found here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sag1/html/sag1/sag1462.htm.
  4. Check the monitor tables installed on the database server by running the following command:
    SELECT * FROM monTables
    If the query returns an error, examine this error for troubleshooting clues. If monitor tables are not installed, you should manually install them. Installation instructions can be found here: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20022_1251/html/monitoring/monitoring33.htm.

Credentials

Database user name and password.

Monitored Components

For details on monitors, see SAM Component Monitor Types.

Components without predetermined threshold values provide guidance such as "Use the lowest threshold possible" or "Use the highest threshold possible" to help you find a threshold appropriate for your application.

Transactions/sec

This component monitor returns the transactions rate, per second.

Days Running

This component monitor returns the number of days the Adaptive Server has been running.

Deadlocks

This component monitor returns the total number of deadlocks that have occurred. This number should be as low as possible.

Active Connections

This component monitor returns the number of active inbound connections.

Lock Waits

This component monitor returns the number of processes that have waited longer than LockWaitThreshold seconds (10 seconds by default).

Lock Waits/sec

This component monitor returns the lock waits rate, per second.

Currently Opened Databases

This component monitor returns the number of currently opened databases.

Active Worker Processes

This component monitor returns the number of active worker processes.

Memory Used by Worker Process (kb)

This component monitor returns the amount of memory, in kilobytes, currently in use by worker processes.

Memory Used by Cache (kb)

This component monitor returns the number of kilobytes of the cache the object is occupying.

Active Processes

This component monitor returns the number of currently active processes.

Specific Database Used Space (MB)

This component monitor returns the used size, in MB, for the specified database.

By default, this monitor returns the value for the master database. To change the database, replace "master" with your database name in the following SQL query: select @dbn = 'master'

Specific Database Free Space (MB)

This component monitor returns the available space, in MB, for the specified database.

By default, this monitor returns the value for the master database. To change the database, replace "master" with your database name in the following SQL query: select @dbn = 'master'

Total Databases Size (MB)

This component monitor returns the total size of all databases, in MB.

Requests/sec

This component monitor returns the rate of stored procedures requested per second.

Procedure Cache Hit Ratio (%)

This component monitor returns the procedure cache hit ratio. This value should be as high as possible.

Cache Hit Ratio (%)

This component monitor returns the percentage of times a requested data page has been found in the data cache. A cache hit ratio of 97% or less could indicate memory starvation. If this becomes a continual issue, allocate more memory to the data cache.

Writes/sec

This component monitor returns the number of buffers written from the cache to the disk.