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:

select latency, *
from sys.dm_cdc_log_scan_sessions

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.

-- Change to the database that contains your capture instance
use YourDatabaseHere
go
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:

exec sys.sp_cdc_change_job @job_type = 'cleanup'
						,@retention = 4320 -- Number of minutes to retain (72 hours)
						,@threshold = 5000

To view the results of the changes, execute the following:

-- View the results of the changed parameter is the cdc_jobs table
SELECT *
FROM msdb.dbo.cdc_jobs