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.