How to improve performance of Post in a Change History or Change Data (CDC) environment (4302344)

While most of the topologies may have common tuning principles, the CDC works differently. In CDC all DML that occur on target are just INSERTs and there are no constraints/indexes involved so any tuning from that angle is ruled out, something that forms basis for tuning in a conventional SharePlex and database instance. There are still ways to squeeze performance and this article explores those options.

General information.

In a CDC environment, the information logged in the target database can be much larger than the changes occurring on the corresponding source. This can sometimes be a drag to the performance. There are ways to curtail some of the logging but such curtailment is a policy matter. Things that can make a difference include logging of COMMITs, number of metadata columns included on target, tracking pre-image of the DML, type of DML operations which need to be tracked, among others.

If you wish, you can do away with logging the COMMITs. In some sites COMMITs form a large % of one’s operations, so it may help. To disable the logging of COMMITs, you can set the parameter SP_OPO_TRACK_COMMITS to 1 on target. For details on the parameter please refer to SharePlex Administrator Guide and SharePlex Reference Guide.

You can reduce the # of metadata columns that are added to the target table by editing the script  add_change_tracking_columns.sql located in product_dir/util directory. The script is customizable. For details on the script, please refer to SharePlex Administrator Guide chapter titled “Configure replication to maintain a change history target”.

By default, SharePlex will only include changed columns of an operation in a CDC target. It is possible to include all columns of an operation and not just changed columns but this approach will incur additional overhead so for performance gains one can limit the CDC to include only rows with changed values. It can make a lot of difference for tables having numerous columns. The parameter SP_OCT_INCLUDE_UNCHANGED_COL governs the behavior and a value of 0 (off) would only include changed columns. Please refer to SharePlex Administrator Guide and SharePlex Reference Guide for details.

By default, a CDC target will track all type of DML, namely INSERT, UPDATE and DELETE. If you are only interested in tracking a particular type of DML, you can limit a CDC target to log that DML operation only. This would obviously provide additional performance gain. The parameter to implement this is SP_OPO_TRACK_OPERATIONS. Please refer to SharePlex Administrator Guide and SharePlex Reference Guide for details.

In summary, additional info request in a CDC target will come with additional cost. It is akin to running audit or supplemental logging in a database where one needs find a trade-off for getting optimum results.