Sybase Statistics Monitoring

Background

Ignite 8.1.300 (and higher) supports the collection of Sybase statistics for queries run from stored procedures.

Ignite retrieves statistics from the monSysStatement table, which exists in Sybase versions12.5.0.3 and higher.  Newer Sybase versions also have a RowsAffected column.  If the RowsAffected column exists, Ignite will include it and display it as Rows Processed.

When Sybase Statistics collection is enabled in Ignite, Ignite  will automatically detect the existance of the monSysStatement table and the RowsAffected column.

Implications due to limitations of the monSysStatement table:

• Sybase will prefix the stored procedure name with "RPC" depending on the client tool used to call the procedure.  If the Ignite option "SYBASE_INCLUDE_SQLTEXT_WITH_PROCEDURE" is enabled, statistics will not be collected for these stored procedures.

• Statistics for Sybase "grouped stored procedures" will be grouped together and reported for one procedure.

Enabling Statistics Monitoring

Sybase statistics collection is disabled by default.  To enable collection in Ignite:

STEP 1: Set the following configuration options in Sybase:

sp_configure 'enable monitoring', 1;
sp_configure 'statement statistics active', 1;
sp_configure 'per object statistics active', 1;
sp_configure 'statement pipe active', 1;
sp_configure 'statement pipe max messages', <number of statement statistics messages stored>;

     ** Setting these configuration options can impact the performance of the Sybase instance.

statement pipe max messages

The setting for statement pipe max messages dictates the maximum number of rows Sybase can store in monSysStatement. Set the value to at least the number of queries that could ever run in the period of time defined by the Ignite statistics collection interval (default is 30 seconds).  If the value is not large enough, statistics reported by Ignite can be either low or missing for a given stored procedure query.

 

Test for adequate buffer size: One way to test whether the statement pipe max messages setting is adequate is to open a connection and, at intervals equal to the Ignite statistics collection interval (default is 30 seconds), query the number of monSysStatement rows:

     select count(1) from monSysStatement;

If the count is sometimes equal to the statement pipe max messages buffer setting, then tuning is recommended to ensure all statistics data is captured.  There are 2 tuning options:

1. Increase the statement pipe max messages setting to hold more statistics rows, or
2. Decrease the stats collection interval so statistics are queried more often (see instructions below).

 

Sybase SQL Stats Tuning Assistant: Ignite includes a SQL Stats Tuning Assistant feature that will perform the above test during every stats poll.  The SQL Stats Tuning Assistant should run during the period of time that Sybase experiences the greatest number of query executions.  Every hour, the SQL Stats Tuning Assistant will write a message in the Ignite message log indicating either "tuning ok" or "tuning recommended" (see the above tuning options).  Enable the tuner by setting the SYBASE_ENABLE_SQLSTATS_TUNER Advanced Options parameter to true.  Note: Statistics monitoring must be enabled for the SQL Stats Tuning Assistant to function.

STEP 2: Set the Advanced Options parameter SYBASE_ENABLE_SQLSTATS to true:

1. In Ignite, click on Options, then on Advanced Options.
2. At the top of the page, check the Support Options checkbox.
3. In the Monitored Database Instance dropdown, choose the Sybase instance.
4. Edit the SYBASE_ENABLE_SQLSTATS, set the new value to True and press OK.
5. Go back to the Ignite Home page and restart the Ignite monitor for the Sybase instance.

Note: To turn statistics collection off, follow the above steps to set SYBASE_ENABLE_SQLSTATS to False.

Optional: Setting the Stats Collection Interval

By default, Ignite collects statistics every 30 seconds (instead of every 10 minutes as is done for other types of databases).  This more frequent statistics collection is done to limit the likelihood that the Sybase statistics cache will become full (when it fills, Sybase flushes rows from from the cache), preventing Ignite from being able to collect statistics for some executions of stored procedure queries.

Basically, the fewer rows allowed in monSysStatement, the more often Ignite should fetch statistics.  To configure Ignite's statitics collection frequency, change the following Advanced Option parameter:

• SYBASE_INTERIM_SQLSTATS_INTERVAL