June 11, 2025 at 12:47 pm
Anyone got any good advice for performance tuning CDC on a Managed Instance?
We are seeing around a 30-35% performance degradation (mainly runtime), with CDC enabled in test.
Done the usual, put the change tracking tables on its own file and file group, set the capture job to poll every second (to try and keep ADR happy also and log truncation so it can keep up with aggressive log truncation part).
Tested with @support_net_changes both 0 and 1.
When checking with sp_whoisactive the waits I see are primarily HT type waits but are very short lived.
We sized up the DB too, to prevent file growth as it grows about 1GB per load test. So sized it up to 25GB per file (primary and cdc file and log).
Just no matter what we do or what MI we run it on at different compute levels, the bottleneck is always there at around 30%.
Get CDC isn’t 0 impact, but never seen it be a 30% impact before.
June 12, 2025 at 1:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 13, 2025 at 9:52 am
Anyone got any good advice for performance tuning CDC on a Managed Instance?
We are seeing around a 30-35% performance degradation (mainly runtime), with CDC enabled in test.
Done the usual, put the change tracking tables on its own file and file group, set the capture job to poll every second (to try and keep ADR happy also and log truncation so it can keep up with aggressive log truncation part).
Tested with @support_net_changes both 0 and 1.
When checking with sp_whoisactive the waits I see are primarily HT type waits but are very short lived.
We sized up the DB too, to prevent file growth as it grows about 1GB per load test. So sized it up to 25GB per file (primary and cdc file and log).
Just no matter what we do or what MI we run it on at different compute levels, the bottleneck is always there at around 30%.
Get CDC isn’t 0 impact, but never seen it be a 30% impact before.
I have implemented in On-prem and observed about 10% bump in resource utilization. Are you expecting heavy INsert\update on DBs and that too on LOB columns?
June 13, 2025 at 2:33 pm
We narrowed it down to the IO characteristics of Managed Instances.
Seems for our load test file, we need to increase the log from 1GB to 576GB to reach the level of IOPS/TPut to make it worth while at around a 5-7% perf hit.
But seems if we take it to the 1026GB level the CDC operation and load is happening faster than non CDC even with a 1026GB log file.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply