Tuning MS SQL Server for Oracle GoldenGate 12.2 Replicat Performance

Performance problems are typically more likely to be manifested in the Replicat rather than the Extract processes.  This is due to the somewhat sequential nature of the GoldenGate Replicat.  The source database is being accessed by many users simultaneously and experiences database changes in parallel.  In addition, a single UPDATE or DELETE statement that touches many rows is converted in GoldenGate as multiple operations in the trail file,  i.e. –  An update that affects 500,000 rows is converted into 500,000 individual update operations based on the primary key.

These changes are extracted by the GoldenGate Extract process and are stored in the trail files based on the order that the transaction was committed.  The Replicat then applies the changes to the target in the order that the transactions are entered into the trail file.  By default the Replicat is a single threaded operation.  Because of this, the Replicat can be the slowest link in the chain of Extract, Pump and Replicat.

There are however several things that you can do to improve Replicat performance for a SQL Server target database.  These performance improvements include both SQL Server and GoldenGate tuning.  They include the following:

Tune SQL Server Indexes – The more indexes that exist on a table, the more overhead is incurred on Insert, Update and Delete statements.  For each of Insert and Delete statements all of the indexes have to be updated.  For Update statements all of the affected indexes have to be updated.

Tune SQL Server Clustered Index – The SQL Server Clustered Index must be wisely created.  This will not only affect the performance of the clustered index, but all non-clustered indexes as well.  The clustered index must be as selective as possible and as efficient as possible.  Do not use surrogate keys for clustered indexes.

Reduce Conflict Detection and Resolution – CDR is an important part of bi-directional replication and is necessary for maintaining bi-directional consistency.  However, CDR should be used only where necessary.  History tables, auditing tables and other INSERT only tables do not require before images.  Wherever before images and COMPARECOLS are not needed, don’t use them.  Use only when needed.

Use BATCHSQL to use array operations – The GoldenGate BATCHSQL parameter allows for SQL Server to take related SQL Statements and pull them together into array operations.  When this is viable, it can result in significant performance improvements.  However, not all operations can be constructed into arrays.  BATCHSQL does not work with very large transactions.  In addition, BATCHSQL is not as effective with COMPARECOLS and before images.

Use Coordinated Replicat – With Coordinated Replicat it is possible to parallelize the Replicat operations by creating multiple threads to perform the INSERT, UPDATE and DELETE operations.  When using coordinated Replicat a coordinator thread distributes trail file transactions among multiple threads thus assuring that proper ordering is maintained.  By parallelizing the operations we can achieve more concurrency and higher performance.

There are several ways to improve performance in SQL Server for the GoldenGate Replicat process.  Foremost you must tune the SQL Server system for improved INSERT, UPDATE and DELETE performance.  In addition there are some GoldenGate Replicat tuning that can be done.