Impact of CDC on SQL Server
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: CDC enabled: Thanks for answers and suggestions. PeterLesson Learned #352: Could not update the metadata that indicates database enabling CDC.
Today, we got a error message while trying to enable cdc for a database using the sqladmin user. Our customer got the error message: Msg 22830, Level 16, State 1, Procedure sys.sp_cdc_enable_db_internal, Line 283 [Batch Start Line 0] Could not update the metadata that indicates database XYZ is enabled for Change Data Capture. The failure occurred when executing the command 'SetCDCTracked(Value = 1)'. The error returned was 33171: 'Only active directory users can impersonate other active directory users.'. Use the action and error to determine the cause of the failure and resubmit the request.Capture Changed Data From your Cosmos DB analytical store (Preview)
Making it super-easy to create efficient and fast ETL processing the cloud, Azure Data Factory has invested heavily in change data capture features. Today, we are super-excited to announce that Azure Cosmos DB analytics store now supports Change Data Capture (CDC), for Azure Cosmos DB API for NoSQL, and Azure Cosmos DB API for Mongo DB in public preview!
