After setting up change data capture (CDC), there are a few configuration options that allow for optimal performance based upon applications load.
Using the default values, the most number of transactions that can be grabbed every 5 seconds is 5000. Remember, a transaction is not the same as a database row. One transaction can update “n” number of rows.
In order to determine latency for you CDC solution, you will need to query a DMV view:
The latency will be shown in seconds, if it is getting too high, start increasing maxtrans, maxscans, and decrease the polling interval.
- polling interval – (default 5 seconds) the amount of time to wait between log scans
- maxtrans – (default 500) the number of transactions to grab with each scan
- maxscans – (default 10) the number of scans performed between each polling interval
In order to change these settings, use the system stored procedure (sys.sp_cdc_change_job) after changing the database which contains your capture instance. The CDC job needs to be restarted after making these changes.
EXEC sys.sp_cdc_change_job @job_type = 'capture'
,@maxtrans = 501
,@maxscans = 10
,@continuous = 1
,@pollinginterval = 5
Changing the latency between the cleanup is done using the same stored procedure:
,@retention = 4320 -- Number of minutes to retain (72 hours)
,@threshold = 5000
To view the results of the changes, execute the following: