Change Tracking Performance

Question

We have a large production system and implemented change tracking 8 days ago with a retention policy of 3 days.  The syscommittab is currently at 332 million records and climbing.  We have SQL 2008 SP1 CU 7 installed (10.0.2766.0).  We temporarily changed the retention policy to 10 days but scaled it back again yesterday becuase the problem with our synchronization process was solved.  We have seen no noticable change in the size of the syscommittab.  How can someone reliable tell if the auto clean-up is working?  (it is on)

More importantly we are seeing a very severe performance degradation on production when querying the change tracking tables.  The following query a week ago ran in seconds, now it takes 15-20 minutes and climbing rapidly.  The table that is being queryed has only 12 rows of data in the internal change tracking table which represents it.  It is a very static table. 

This Query takes 15-20 minutes to run on production:

SELECT

 

* FROM CHANGETABLE(CHANGES dbo.[table1], 146316152) AS c LEFT OUTER JOIN dbo.[table2] AS a

WITH

 

(NOLOCK) ON c.[CID] = a.[CID] AND c.[DID] = a.[DID] WHERE

(

 

c.SYS_CHANGE_OPERATION = 'D' OR (a.[CID] IS NOT NULL AND a.[DID] IS NOT NULL));

This Query takes only 43 seconds:

SELECT

 

* FROM CHANGETABLE(CHANGES dbo.[table1], null) AS c LEFT OUTER JOIN dbo.[table2] AS a

WITH

 

(NOLOCK) ON c.[CID] = a.[CID] AND c.[DID] = a.[DID] WHERE

(

 

c.SYS_CHANGE_OPERATION = 'D' OR (a.[CID] IS NOT NULL AND a.[DID] IS NOT NULL)) AND SYS_CHANGE_VERSION > 146316152 ;

The difference in the execution plan is the first one does an index seek on syscommittab using some scaler operator in the predicate where the second FASTER query uses an index scan against syscommittab.  We are contemplating going to the second syntax to perform our data extracts but are unsure where the two queries return the same result set and if not what is the difference?

Any help would be appreciated, this is quickly becoming a crisis.

Answer 1

Hello,

I just want to bounce this as it seems to me this was never solved. We are running in the same problems.

Our server is SQL 2008 Standard R2 CU7 (10.50.1777). It should therefore already include the hotfix http://support.microsoft.com/kb/2276330.

Our database is ways smaller. We have 77 tables, the sys.syscommittab has ~ 3 million rows.

Simple calls to changetable last very long, although returning no or just a hand full of rows.

Queries for initial syncs lead to timeout problems eg: select * from changetable (changes tbl_Auftragsdatenfelder, null) ct

What makes this a severe issue for us is that we are running a sync scenario with occassionally connected clients on Windows Mobile devices (therefore only Sync Services 1.1 for devices is possible). As the sync by its nature runs lots of queries (6 queries per table per batch in worst case) if the performance decreases some seconds per query, the overall bad performance can lead to timeout problems in the sync service (webservice).

One thing that is a known potential issue is that we have a very long retention period (360 days). Problem is, that we have data that old (could even go beyond) that need to be synced. And as the out-of-box batching support in Sync Services 1 is based on the anchor values, masterdata that have cleanedup change history can no longer be batched which in turn leads the fatal memory problems in the limited Windows mobile world.

Any hints are highly appreciated.

Regards,

Andreas

Answer 2

This issue is still not solved. We have SQL2014 with 160 million rows set to clean up every 7 days.

I ran the manual change tracking cleaning (sp_flush_CT_internal_table_on_demand) which didn't seem to dent the 160 million count.

Something is very wrong...

Answer 3

I just had an idea. Does change tracking erroneously include updates when an index reorganization/rebuild is performed?

Answer 4

Bo, but it has to scan through the log finding commands to populate to the change tracking tables. So re-indexing does slow it down. Keeping the number of VMFs low does help.

Answer 5

Hi Brain2000, I was wondering if you were able to resolve your issue?if yes, can you share it here please? I have the same issue, where auto cleanup for change tracking is not working and I have ran the stored proc (sp_flush_CT_internal_table_on_demand) and did not delete the change tracking records.

Answer 6

Hi

-- show the tracked items
select * from changetable(changes dbo.t, NULL) as ct;
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

/*
Received message
(0 row(s) affected)
The Change Tracking manual cleanup procedure for database cttest has been executed
*/

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it off !
alter database cttest set change_tracking (auto_cleanup=off);
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it back on, and wait
alter database cttest set change_tracking (change_retention = 1 minutes, auto_cleanup = on)
go

Fore more details

https://dba.stackexchange.com/questions/94141/change-tracking-cleanup

Thanks and regards