SQL Server › Change Data Capture (CDC)

What are the performance and storage considerations to keep in mind before enabling Change Data Capture on a very high volume table?

Advanced

Answer

CDC adds some overhead to every insert, update, and delete since it must also write to the change table, and the change tables themselves consume additional storage that grows continuously until old changes are cleaned up, so you should monitor the cleanup job's schedule and retention period carefully on very high volume tables.

EXEC sys.sp_cdc_change_job
  @job_type = 'cleanup',
  @retention = 4320; -- retention in minutes

Real-world example A high traffic order processing system carefully tunes its CDC cleanup job retention period after noticing the change tables were growing faster than expected, balancing how long change history is kept against available storage.

Common follow-ups: How do you monitor how much storage CDC change tables are actually consuming?;What happens if the CDC cleanup job falls behind on a very busy system?

Query Optimization & Plans;Backup & Recovery