Submit a ticketCall us

Don’t fall victim to a ransomware attack
Backups are helpful, but sometimes that’s not enough to protect your business against ransomware. At our live webcast we will discuss how to protect against ransomware attacks with SolarWinds® Patch Manager and how to leverage log data to detect ransomware. Register now for our live webcast.

Home > Success Center > Database Performance Analyzer (DPA) > Sybase statistics monitoring

Sybase statistics monitoring

Background

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 12.5.0.3 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 prefixes the stored procedure name with "RPC" depending on the client tool used to call the procedure. If the DPA advanced option SYBASE_INCLUDE_SQLTEXT_WITH_PROCEDURE is enabled, statistics are not collected for these stored procedures.
  • Statistics for Sybase "grouped stored procedures" are grouped together and reported for one procedure.

Enabling Statistics Monitoring

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

 

Step 1: Configure 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/>;

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:

  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

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.

Step 2: Enable SQL stats   

  1. In DPA, click Options > Administration > Advanced Options.
  2. Click the DB Instance Options tab.
  3. Select the Support Options check box.
  4. Select the Sybase instance from the Database Instance list.
  5. Click the SYBASE_ENABLE_SQLSTATS option, set the New Value to True, and click Update.
  6. Go to the DPA home page, and stop and then start the monitor from the Action menu for the Sybase instance.

 

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

Optional: Set the stats collection interval

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.

Last modified
19:18, 22 Jun 2016

Tags

Classifications

Public