ReplTip – Measuring Latency in Change Data Capture (CDC)

By Sateesh Yele, Microsoft SQL Escalation Services

This DMV sys.dm_cdc_log_scan_sessions is very useful in determining the latency with Change Data Capture (CDC).

I inserted one record in the database and started the CDC capture job after around 2 minutes. It showed the latency as 165 seconds. It is difference in seconds, between end_time and last_commit_cdc_time in the session

session_id        start_time        end_time        duration      scan_phase      last_commit_time        log_record_count        schema_change_count        command_count      last_commit_cdc_time        latency

1        2015-05-20 14:02:05.297        2015-05-20 14:02:05.540        0        Done       2015-05-20 13:59:20.120        1        0        1        2015-05-20 13:59:20.120 165        0        0

See this article for more details: https://msdn.microsoft.com/en-ca/library/bb510694.aspx

last_commit_cdc_time — Time the last commit log record was processed that contained change data capture transactions.
end_time                        — Time the session ended.