Change Data Capture On Production SQL Server 2012

Question

I plan to enable CDC on a SQL Server 2012 production instance.
The CDC tasks will include 100+ tables but the write QPS is well bellow 1k. The hardware of the server is pretty updated so its performance is very good.
I know a very common practice is to enable CDC on a db slave instance. However we do not have enough resource yet.
I would like to ask how much burden will the CDC tasks put on the production instance? Do enabling CDC tasks have any other side-effects other than performance?
Thank you very much.

Answer 1

Hi @B W ，

The performance of CDC itself is determined by the difference between the time when the original transaction happened in the database and the time the change record appears in the change table, the latency can be determined using the dynamic management view sys.dm_cdc_log_scan_sessions:
https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/bb510694(v=sql.100)

CDC has a very low impact on the application workload and can therefore be used without negatively impacting application performance on a system with sufficient I/O capacity. In most test cases, CDC incurred only a very small increase in processor usage

https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd266396(v=sql.100)?redirectedfrom=MSDN

Additional:Administer and monitor change data capture

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

CDC is an asynchronous process. It adds very little overhead to the system.