I have setup up SQL Server Change Tracking on a table t1 on MS SQL2008R2. Within a transaction, I insert a row into it and update it 'n' times. When I examine the rowcounts in the change tracking tables, it shows 'n' number of rows whereas the syscommitab shows just 1 row. Since the change tracking mechanism is a synchronous mechanism and per documentation, is expected to track only "net change" which occurred in a transaction, why is the change tracking table showing 'n' rows instead of just 1 ? Here is the SQL that was used to examine the rowcount.
SELECT sct1.name as CT_schema, sot1.name as CT_table, ps1.row_count as CT_rows, ps1.reserved_page_count*8./1024. as CT_reserved_MB, sct2.name as tracked_schema, sot2.name as tracked_name, ps2.row_count as tracked_rows, ps2.reserved_page_count*8./1024. as tracked_base_table_MB, change_tracking_min_valid_version(sot2.object_id) as min_valid_version FROM sys.internal_tables it JOIN sys.objects sot1 on it.object_id=sot1.object_id JOIN sys.schemas AS sct1 on sot1.schema_id=sct1.schema_id JOIN sys.dm_db_partition_stats ps1 on it.object_id = ps1. object_id and ps1.index_id in (0,1) LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id LEFT JOIN sys.schemas AS sct2 on sot2.schema_id=sct2.schema_id LEFT JOIN sys.dm_db_partition_stats ps2 on sot2.object_id = ps2. object_id and ps2.index_id in (0,1) WHERE it.internal_type IN (209, 210) and (sot2.name='t1' or sot1.name='syscommittab')
Above SQL courtesy of https://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/