Change Tracking Cleanup – Part 2

In the first part of my Change Tracking Cleanup post, I talked about how automatic and manual cleanup happens in SQL Server. In this post, we will explore a bit more in depth on how the cleanup actually works with the help of some metadata from an actual Change Tracking implementation.

As mentioned in my last post, ChangeTracking auto cleanup is a background thread which wakes up at a fixed frequency and purges expired records (records beyond retention period) from the change tracking side tables. There are two cleanup versions that this thread maintains over the course of the cleanup action – invalid cleanup version and hardened cleanup version. When the thread wakes up, it determines the invalid cleanup version. The invalid cleanup version is the change tracking version which marks the point till which the auto cleanup task will perform the cleanup for the side tables. The autocleanup thread traverses through the tables that are enabled for change tracking and calls an internal stored procedure in a while loop, deleting 5000 rows in a single call within the while loop. The loop is terminated only when all the expired records in the side table are removed. This delete query uses the syscommittab table (an in-memory rowstore ) to identify the transaction IDs that have a commit timestemp less than the invalid cleanup version. This process is repeated until the cleanup is done with all change tracking side tables for that particular database. Once this is done with the final change tracking side table, it updates the hardened cleanup version to the invalid cleanup version.

Every time a checkpoint is run, an internal procedure is called that uses the hardened cleanup version and deletes a minimum of 10k records from sys.syscommittab table after they are flushed to the disk-based side tables. As you can see, both cleanup (in-memory rowstore and disk based side tables) are inter-dependent and having an issue with one of these might affect the other cleanup, eventually leading to unnecessary records in sys.syscommittab and delays in CHANGETABLE functions. See screenshot below of an extended event session tracing the checkpoint of a database which shows operations on the sys.syscommittab table.

Below is the output of calling the stored procedure for manual cleanup stored procedure, "sp_flush_CT_internal_table_on_demand". I had inserted 50K rows and random updates to three tables t2, t3 and t4. The change data was cleaned up by the automatic cleanup post the retention period. Post the cleanup, I inserted another 50K rows into the table t2. After that I executed the manual cleanup procedure which did not have any cleanup to perform as the change data was within the retention period.

Cleanup Watermark = 103016

Internal Change Tracking table name : change_tracking_885578193

Total rows deleted: 0.

-- Query to fetch cleanup version for a change tracking table

select

object_name (object_id) as table_name,

is_track_columns_updated_on,

min_valid_version,

begin_version,

cleanup_version

from sys.change_tracking_tables

The screenshot of the SSMS output grid that you see below is from the query above.

A new extended event, "change_tracking_cleanup", was added to track change tracking automatic cleanup activities. The T-SQL script used to fetch the information below can be found on our tigertoolbox github repository.

As you can see from the screenshot below, the cleanup task shows you when the cleanup started and completed. Additionally, you get granular details like when the retention timestamp was updated which is an easy way of co-relating the invalid cleanup version to a timestamp value (see UpdateRetention and UpdateInvalidCleanup steps below). The side table object IDs shown below have line items reflecting the number of rows cleaned up and the start and end of the change tracking cleanup. One aspect to keep in mind is that the update retention timestamp is reflected in UTC and you will need to do the necessary conversion to get the time aligned with the server's local timezone.

To summarize, we suggest the following steps when troubleshooting change tracking cleanup issues:

1. Ensure that auto cleanup is working properly using the Extended Event "change_tracking_cleanup"

2. If automatic cleanup is running slowly, then you can execute the stored procedure "sp_flush_CT_internal_table_on_demand". In SQL Server 2014 Service Pack 2 and above, we provided a new Stored Procedure, sp_flush_CT_internal_table_on_demand, to assist with Change Tracking cleanup. KB3173157 has more details.

Amit Banerjee (@banerjeeamit)