Impact of CDC on SQL Server

Copper Contributor

Hi,


We are testing performance impact of CDC on SQL Server.


There are two identical databases (KST_S001, KST_002) on SQL Server 2017 which is running in linux container. They both have CDC enabled for 180 tables and a data generator that is doing mostly updates on these tables.

The data generators are doing around 300k DML operations per minute on each database (600k total).

The configuration of CDC jobs is kept default (500 transactions, 5s polling interval for capture job, I think three days retention period on cleaning job)

The host machine is Azure Standard_E8-4as_v4: 4 vCPU, 64 GB RAM, 12800 IOPS, 128GB SSD.

 

After 1 hour of running the setup with CDC enabled, the average time of 1000 updates is 73ms.

With CDC disabled the average time of 1000 updates after 1 hour is 15ms.


I've attached two grafana screenshots showing the difference in metrics when the CDC is disabled versus enabled on these databases.


We are trying to understand the underlying mechanism that is contributing to the impact visible from the metrics.

Why is memory consumption so much higher?

Why do DML operations take longer with CDC enabled?

 

CDC disabled:

PeterK32_0-1700732231037.png

CDC enabled:

PeterK32_1-1700732254186.png

 

Thanks for answers and suggestions.

Peter

1 Reply

@PeterK32 Hello there!

CDC is (allegedly) "low impact", not "zero impact" - it sounds like you are simulating a very busy transactional system. DML operations are necessarily going to take longer when you have the CDC process also scanning the log files.

How much longer? That depends.

There really isn't a lot of details forthcoming from Microsoft (or anywhere) regarding metrics for what kind of impact you can expect, which is understandable to a degree, since every situation is different.

Still, it would be nice to have some "official" steps or metrics that can be gathered to measure this.

In our testing (with considerably less activity than yours), we are seeing mainly a CPU increase, not memory.

Are you making sure to back up the logs frequently if running in full recovery? If not, the inactive portion of the log won't get released for reuse, and CDC will have to scan an increasingly large log file (at least that's my understanding, I could be wrong).

 

Have you experimented with different Polling Intervals etc.?

 

Good luck!