sql - 变更数据捕获性能损失是否仅限于启用 CDC 的表？

Question

我读到，启用变更数据捕获显然会对数据库性能产生影响。这种性能损失是否仅影响启用了 CDC 的表，还是会影响数据库中的所有操作

就我而言，我使用的是 SSIS，并且有大量数据移入和移出临时数据库。我的系统中还有一些用于转换的查找表。我希望使用 CDC 作为仅审核这些引用表(而不是导入的数据)的更改的方法。我作为 ETL 的一部分运行的主要查询访问这些引用表，但不更改它们，因此我试图弄清楚是否仍然会有明显的性能影响？

谢谢

Answer 1

这个问题的答案既是肯定的，也是否定的。

不，因为

When a table is enabled for change data capture, an associated captureinstance is created to support thedissemination of the change data inthe source table. The capture instanceconsists of a change table and up totwo query functions.

未跟踪的表似乎不参与跟踪数据。

是的，因为

The source of change data for changedata capture is the SQL Servertransaction log. As inserts, updates,and deletes are applied to trackedsource tables, entries that describethose changes are added to the log.The log serves as input to the changedata capture process. Thisreads the log and adds informationabout changes to the tracked table’sassociated change table.

由于更改的来源来自事务日志，因此更改的传播需要捕获实例读取并解释事务日志(免责声明:我对事物的解释)。仅启用 CDC 就会对整个数据库产生性能影响。

建议

Storage:

• When planning change data capture architecture, take a significant increase in log size and log volume I/O operations into account.
• Consider specifying a filegroup in sys.sp_cdc_enable_table.
• 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_mappings are located on a different filegroup than PRIMARY.

Workload behavior:

• Try to avoid scenarios where a row needs to be updated immediately after insert.
• Try to avoid using change data capture to capture changes to tables that have frequent large update transactions.

Change data capture parameters:

• Always reduce the list of columns captured by change data capture to only the columns you really need to track.
• If you do not require support for net changes, set @ to 0.
• Use to see whether change data capture can keep up with your workload.
• If change data capture cannot keep up with your workload, modify scan job parameters and restart the scan job.

结论
如果您的服务器当前在跟上其负载方面没有问题，我非常怀疑您是否会注意到为不经常更改的表启用 CDC 时出现的任何性能问题。

来源

• Basics of Change Data Capture
• Tuning the Performance of Change Data Capture