Change Data Capture Best Practices

Here is a quick list of CDC Best Practices.  Search “CDC” tags for other BLOG posting on designing, supporting and troubleshooting CDC.

These recommendation come from various sources including the MSDN CDC White Paper, a recommended read for anyone interesting in tuning CDC performance.

Recommendation: Try to avoid scenarios where a row needs to be updated immediately after insert which results in 3 CDC tracking rows (new row, previous values, and changed values).

Recommendation: Try to avoid using change data capture to capture changes to tables that have frequent large update transactions.

Recommendation: Always limit the list of columns captured by change data capture to only the columns you really need to track by specifying the @captured_column_list parameter in sys.sp_cdc_enable_table.

Recommendation: If you do not require support for net changes, set @supports_net_changes to 0. If you do require querying for net changes but change data capture latency grows too big, it can be worthwhile to turn support for net changes off and do the net change detection later in a staging database.

Recommendation: If possible, run cleanup when there is no other workload active. Test increasing the threshold parameter until you find a sweet spot for your workload.

Recommendation: To keep the PRIMARY filegroup small and to have a clear distinction between application data and change data, you should specify @filegroup_name in sys.sp_cdc_enable_table.

Recommendation: Consider changing the default filegroup for the database before you execute sys.sp_cdc_enble_db, so that change data capture metadata and especially cdc.lsn_time_mapping are located on a different filegroup than PRIMARY. You can change the default filegroup back after the change data capture metadata tables are created.

Recommendation: For high volume systems, use sys.sp_cdc_change_job to change CDC job parameter to @MaxTrans=5000 and @pollinginterval = 1.