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:

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
Related Posts:
Post a comment

Mailing list

Featured Articles

 Site Author

Resources