DPA 8.1.3 and later supports the collection of Sybase statistics for queries run from stored procedures.
DPA retrieves statistics from the monSysStatement table, which exists in Sybase 126.96.36.199 and later. Later Sybase versions also have a RowsAffected column. If the RowsAffected column exists, DPA includes it and displays it as Rows Processed.
When you enable Sybase statistics collection in DPA, DPA automatically detects the existence of the monSysStatement table and the RowsAffected column.
Implications due to limitations of the monSysStatement table:
Sybase statistics collection is disabled by default. To enable collection in DPA:
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/>;
Note: Setting these configuration options can affect 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 DPA statistics collection interval (default is 30 seconds). If the value is not large enough, statistics reported by DPA 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 DPA 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:
Sybase SQL stats tuning assistant
DPA 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 DPA 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.
Note: To turn statistics collection off, follow the above steps to set SYBASE_ENABLE_SQLSTATS to False.
By default, DPA 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 DPA from being able to collect statistics for some executions of stored procedure queries. The fewer rows allowed in monSysStatement, the more often DPA should fetch statistics. To configure the statitics collection frequency, change the SYBASE_INTERIM_SQLSTATS_INTERVAL advanced option.