Analyzing subscription performance

Many factors can contribute to latency. Environmental variables, such as CPU speed, memory allocation and network bandwidth, can affect replication performance. CDC Replication configuration settings may also be a cause.

The following list provides examples of common situations in which you may experience latency:

• Insufficient network bandwidth for the volume of transactions that you are processing is increasing the amount of time between changes on the source table and the same change on the target table. To resolve this problem, contact your network administrator.
• Resource intensive batch jobs or background tasks on either the source or target can result in increased latency for subscriptions until these processes are complete.
• CDC Replication is replicating changes for rows which contain large amounts of data in one or more Large Objects (LOB) columns.
• A database lock contention in which an application on the target database may unexpectedly lock data in the database. This may force CDC Replication to wait for the locks to be released.
• Indexes that are not optimized or lack a primary/unique key. This can cause slow database operations and result in performance issues that surface in CDC Replication.

To determine what is causing the latency to occur, use the Bottlenecks area of the Performance view to identify where bottlenecks are occurring. This view displays a bar chart featuring the five components of the CDC Replication replication pipeline that can be identified as bottlenecks:

Log Reader

Displays bottleneck intervals for the log reader on the source replication engine.

Source Engine

Displays bottleneck intervals for the log parser on the source replication engine.

Communications

Displays bottleneck intervals for communications for the source replication engine.

Target Engine

Displays bottleneck intervals for the target replication engine.

Target Database

Displays bottleneck intervals for the target database.

The results of the bar chart will help you isolate where bottlenecks are occurring. Once you have identified the problem area, you can perform a more in-depth analysis by charting metrics specific to that area.

You can also analyze performance through the use of metrics. The collection of statistical data to analyze performance is available in two modes:

• Live mode allows you to monitor the performance of multiple subscriptions in real time. The performance information will be stored in memory for the period of time defined in the Statistic History Retained (minutes) box on the Preferences dialog.
• Snapshot mode allows you to view the history of one or more selected subscription's performance activity. The first time that you add a subscription in the Performance view and collect data, you will be asked if you want to save the data; if you choose Yes, performance activities will be recorded in a comma-delimited file. The file can then be opened in Snapshot mode for further analysis.

In Snapshot mode, the files are stored locally; each file is given a default name of the subscription name and the start time of the collection period. The maximum number of files that will be retained for each subscription, as well as the maximum size for each file, can be set on the Preferences dialog. When setting these values, you should keep in mind that the number of statistics chosen for collection will affect the file size; a large number of selected statistics means that a file size will become large quickly. When the maximum file size is reached during a collection period, a new file will be created. When the number of files reaches the set limit, the oldest file in the folder will be deleted and a new file created.

If you suspect that individual tables within a subscription are causing latency issues, you can also analyze performance at the table level. CDC Replication provides a list of the source tables in the subscription that are the most active. Before proceeding with table-level analysis, you should be aware that the collection of table-level statistics might require additional resources and can affect system performance.

Additionally, if you want to export the recent performance activity to a file for further analysis, you can use the Export Snapshot feature to send the information collected to a comma-delimited file. This will export the information that is currently stored in memory, as set in the Statistic History Retained (minutes) box on the Preferences dialog.