Database performance issues due to increased data volumes

Poor database performance could also be incurred due to increased data volumes, relative to your database server’s ability, in the IBM® Sterling Control Center Monitor tables to get the performance you require. This especially applies to IBM Sterling Control Center Monitor Summary tables as they cannot be partitioned.

The IBM Sterling Control Center Monitor Summary table names are:
  • ROLL_UP
  • CC_PROCESS
  • CC_PROCESS_DVG
  • CC_FILE_TRANSFER
  • CC_FILE_TRANSFER_DVG

Release 6.1.1.0 and later, allow you to configure IBM Sterling Control Center Monitor to automatically truncate data from after a specified number of days via the Swing console. See System Settings > Database.

TIP: Configuring Data purge settings

By default, the Purge Summary Data Older Than (days) is set to zero. This ensures data is never purged from summary tables. Keeping all summary data can be problematic for performance and you should change this default setting. Before doing so see, Manually Purging Summary Table Data. Note that Purge Summary Data Older Than (days) should be at least as large as Data Older Than (days), but maybe not larger.

When Control Center Monitor is configured to purge summary table data the following two engine properties are utilized:
  • SUMMARY_TABLES_PURGE_BATCH_SIZE
  • SUMMARY_TABLES_PURGE_QUEUE_SIZE

SUMMARY_TABLES_PURGE_QUEUE_SIZE, whose default is 100000, sets the limit on the SQL used to ascertain the data to be deleted from the summary tables.

SUMMARY_TABLES_PURGE_BATCH_SIZE, whose default is 100, is used as a limit in the SQL used to delete summary table data. Since the SQL used to delete data causes far more contention on the database than the SQL used to find the data to be deleted, the value for this property should be kept relatively small.

Note: Just setting Purge Summary Data Older Than (days) to a value greater than zero will not cause all summary data to be purged. Engine property, SUMMARY_TABLES_PURGE_ROLL_UP, with default value is FALSE, is used by IBM Sterling Control Center Monitor to purge data from ROLL_UP when other summary data is purged. Upon setting SUMMARY_TABLES_PURGE_ROLL_UP to TRUE, data from ROLL_UP will be purged at the same time data from the other summary tables is purged.

Database performance is also affected by the amount of data retained in the EVENTS table, and its ancillary tables, which include all the _STATS_LOG , BP_STATS_LOG,CD_STATS_LOG and others. The amount of data retained in these tables may be controlled via the value specified for Data Older Than (days).

TIP: Schedule data Purging time

Data purging should not be scheduled to run during a busy processing time by IBM Sterling Control Center Monitor that is, data purging should not be scheduled when the servers monitored by IBM Sterling Control Center Monitor are at their busiest. The time at which the daily removal of data should be performed should coincide with the time at which IBM Sterling Control Center Monitor is generating the fewest number of events.

The following Microsoft SQL server query returns the number of events generated per hour and can be used to ascertain a good time to schedule the daily data purge:
SELECT convert(char(14), DATE_TIME,126) as dtm, count(EVENT_ID) as number FROM EVENTS
(nolock) group by convert(CHAR(14),DATE_TIME,126) order by dtm