Setting Maximum Scanner Schema Cache Size

To optimize Adaptive Server RepAgent memory consumption and improve replication performance, set the maximum amount of mermory that each scanner thread can use to store object schema required for replication.

1. Set the maximum schema size for each scanner thread at the primary Adaptive Server database running the RepAgent:

   sp_config_rep_agent database_name, 'max schema cache per scanner'[, 'max_schema_cache_per_scanner_value'] 

   The range of values for the maximum scanner schema cache size is 524,288 bytes to the value of MAXINT which is 2,147,483,647 bytes. The default is 524,288 bytes
   For example to set the maximum schema cache size for each RepAgent scanner to 1,048,576 bytes at the pdb1 database, enter:

   sp_config_rep_agent pdb1, 'max schema cache per scanner', '1048576' 

2. Restart RepAgent for the change in max schema cache per scanner to take effect.
   Use sp_config_rep_agent or sp_help_rep_agent to display the RepAgent scanner schema values.
3. Obtain schema cache information for each RepAgent scanner from the MonRepSchemaCache Adaptive Server monitoring table to help you tune the scanner schema size for better replication performance.

   You see a row for each scanner if there are multiple scanners configured for the same database.

   See Adaptive Server Enterprise Performance and Tuning Series: Monitoring Tables to obtain information from monitoring tables.

   See Monitoring Tables in Adaptive Server Enterprise Reference Manual: Tables for the description of the MonRepSchemaCache table schema.