Configure Change Data Capture Settings

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:

[cc lang=”sql”]
select latency, *
from sys.dm_cdc_log_scan_sessions
[/cc]

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.

[cc lang=”sql”]
— 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
[/cc]

Changing the latency between the cleanup is done using the same stored procedure:

[cc lang=”sql”]
exec sys.sp_cdc_change_job @job_type = ‘cleanup’
,@retention = 4320 — Number of minutes to retain (72 hours)
,@threshold = 5000
[/cc]

To view the results of the changes, execute the following:
[cc lang=”sql”]
— View the results of the changed parameter is the cdc_jobs table
SELECT *
FROM msdb.dbo.cdc_jobs
[/cc]