Home
%3CLINGO-SUB%20id%3D%22lingo-sub-385090%22%20slang%3D%22en-US%22%3EChange%20Tracking%20Cleanup%20%E2%80%93%20Part%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-385090%22%20slang%3D%22en-US%22%3E%0A%20%26lt%3Bmeta%20http-equiv%3D%22Content-Type%22%20content%3D%22text%2Fhtml%3B%20charset%3DUTF-8%22%20%2F%26gt%3B%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Jan%2017%2C%202017%20%3C%2FSTRONG%3E%20%3CBR%20%2F%3E%3CP%3EIn%20the%20%3CA%20href%3D%22https%3A%2F%2Fblogs.msdn.microsoft.com%2Fsql_server_team%2Fchange-tracking-cleanup-part-1%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20first%20part%20%3C%2FA%3E%20of%20my%20Change%20Tracking%20Cleanup%20post%2C%20I%20talked%20about%20how%20automatic%20and%20manual%20cleanup%20happens%20in%20SQL%20Server.%20In%20this%20post%2C%20we%20will%20explore%20a%20bit%20more%20in%20depth%20on%20how%20the%20cleanup%20actually%20works%20with%20the%20help%20of%20some%20metadata%20from%20an%20actual%20Change%20Tracking%20implementation.%3C%2FP%3E%0A%20%20%3CP%3EAs%20mentioned%20in%20my%20last%20post%2C%20ChangeTracking%20auto%20cleanup%20is%20a%20background%20thread%20which%20wakes%20up%20at%20a%20fixed%20frequency%20and%20purges%20expired%20records%20(records%20beyond%20retention%20period)%20from%20the%20change%20tracking%20side%20tables.%20There%20are%20two%20cleanup%20versions%20that%20this%20thread%20maintains%20over%20the%20course%20of%20the%20cleanup%20action%20%E2%80%93%20invalid%20cleanup%20version%20and%20hardened%20cleanup%20version.%20When%20the%20thread%20wakes%20up%2C%20it%20determines%20the%20invalid%20cleanup%20version.%20The%20invalid%20cleanup%20version%20is%20the%20change%20tracking%20version%20which%20marks%20the%20point%20till%20which%20the%20auto%20cleanup%20task%20will%20perform%20the%20cleanup%20for%20the%20side%20tables.%20The%20autocleanup%20thread%20traverses%20through%20the%20tables%20that%20are%20enabled%20for%20change%20tracking%20and%20calls%20an%20internal%20stored%20procedure%20in%20a%20while%20loop%2C%20deleting%205000%20rows%20in%20a%20single%20call%20within%20the%20while%20loop.%20The%20loop%20is%20terminated%20only%20when%20all%20the%20expired%20records%20in%20the%20side%20table%20are%20removed.%20This%20delete%20query%20uses%20the%20syscommittab%20table%20(an%20in-memory%20rowstore%20)%20to%20identify%20the%20transaction%20IDs%20that%20have%20a%20commit%20timestemp%20less%20than%20the%20invalid%20cleanup%20version.%20This%20process%20is%20repeated%20until%20the%20cleanup%20is%20done%20with%20all%20change%20tracking%20side%20tables%20for%20that%20particular%20database.%20Once%20this%20is%20done%20with%20the%20final%20change%20tracking%20side%20table%2C%20it%20updates%20the%20hardened%20cleanup%20version%20to%20the%20invalid%20cleanup%20version.%3C%2FP%3E%0A%20%20%3CP%3EEvery%20time%20a%20checkpoint%20is%20run%2C%20an%20internal%20procedure%20is%20called%20that%20uses%20the%20hardened%20cleanup%20version%20and%20deletes%20a%20minimum%20of%2010k%20records%20from%20sys.syscommittab%20table%20after%20they%20are%20flushed%20to%20the%20disk-based%20side%20tables.%20As%20you%20can%20see%2C%20both%20cleanup%20(in-memory%20rowstore%20and%20disk%20based%20side%20tables)%20are%20inter-dependent%20and%20having%20an%20issue%20with%20one%20of%20these%20might%20affect%20the%20other%20cleanup%2C%20eventually%20leading%20to%20unnecessary%20records%20in%20sys.syscommittab%20and%20delays%20in%20CHANGETABLE%20functions.%20See%20screenshot%20below%20of%20an%20extended%20event%20session%20tracing%20the%20checkpoint%20of%20a%20database%20which%20shows%20operations%20on%20the%20sys.syscommittab%20table.%3C%2FP%3E%0A%20%20%3CP%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F98261i23105819E40D4788%22%20%2F%3E%3C%2FP%3E%0A%20%20%3CP%3EBelow%20is%20the%20output%20of%20calling%20the%20stored%20procedure%20for%20manual%20cleanup%20stored%20procedure%2C%20%22sp_flush_CT_internal_table_on_demand%22.%20I%20had%20inserted%2050K%20rows%20and%20random%20updates%20to%20three%20tables%20t2%2C%20t3%20and%20t4.%20The%20change%20data%20was%20cleaned%20up%20by%20the%20automatic%20cleanup%20post%20the%20retention%20period.%20Post%20the%20cleanup%2C%20I%20inserted%20another%2050K%20rows%20into%20the%20table%20t2.%20After%20that%20I%20executed%20the%20manual%20cleanup%20procedure%20which%20did%20not%20have%20any%20cleanup%20to%20perform%20as%20the%20change%20data%20was%20within%20the%20retention%20period.%3C%2FP%3E%0A%20%20%3CP%3E%3CCODE%3E%0A%20%20%20%3C%2FCODE%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CBLOCKQUOTE%3E%0A%20%20%20%3CP%3E%0A%20%20%20%20%3CCODE%3E%0A%20%20%20%20%20Cleanup%20Watermark%20%3D%20103016%0A%20%20%20%20%3C%2FCODE%3E%0A%20%20%20%3C%2FP%3E%0A%20%20%20%3CP%3E%0A%20%20%20%20%3CCODE%3E%0A%20%20%20%20%20Internal%20Change%20Tracking%20table%20name%20%3A%20change_tracking_885578193%0A%20%20%20%20%3C%2FCODE%3E%0A%20%20%20%3C%2FP%3E%0A%20%20%20%3CP%3E%0A%20%20%20%20%3CCODE%3E%0A%20%20%20%20%20Total%20rows%20deleted%3A%200.%0A%20%20%20%20%3C%2FCODE%3E%0A%20%20%20%3C%2FP%3E%0A%20%20%3C%2FBLOCKQUOTE%3E%0A%20%20%3CP%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20--%20Query%20to%20fetch%20cleanup%20version%20for%20a%20change%20tracking%20table%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20select%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20object_name%20(object_id)%20as%20table_name%2C%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20is_track_columns_updated_on%2C%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20min_valid_version%2C%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20begin_version%2C%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20cleanup_version%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20from%20sys.change_tracking_tables%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20The%20screenshot%20of%20the%20SSMS%20output%20grid%20that%20you%20see%20below%20is%20from%20the%20query%20above.%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F98262i30F54AFC2CDAF822%22%20%2F%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20A%20new%20extended%20event%2C%20%22change_tracking_cleanup%22%2C%20was%20added%20to%20track%20change%20tracking%20automatic%20cleanup%20activities.%20The%20T-SQL%20script%20used%20to%20fetch%20the%20information%20below%20can%20be%20found%20on%20our%0A%20%20%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FMicrosoft%2Ftigertoolbox%2Fblob%2Fmaster%2Fchange-tracking%2FReadChangeTrackingXEData.sql%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%0A%20%20%20%20tigertoolbox%20github%0A%20%20%20%3C%2FA%3E%0A%20%20%20repository.%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20As%20you%20can%20see%20from%20the%20screenshot%20below%2C%20the%20cleanup%20task%20shows%20you%20when%20the%20cleanup%20started%20and%20completed.%20Additionally%2C%20you%20get%20granular%20details%20like%20when%20the%20retention%20timestamp%20was%20updated%20which%20is%20an%20easy%20way%20of%20co-relating%20the%20invalid%20cleanup%20version%20to%20a%20timestamp%20value%20(see%20UpdateRetention%20and%20UpdateInvalidCleanup%20steps%20below).%20The%20side%20table%20object%20IDs%20shown%20below%20have%20line%20items%20reflecting%20the%20number%20of%20rows%20cleaned%20up%20and%20the%20start%20and%20end%20of%20the%20change%20tracking%20cleanup.%20One%20aspect%20to%20keep%20in%20mind%20is%20that%20the%20update%20retention%20timestamp%20is%20reflected%20in%20UTC%20and%20you%20will%20need%20to%20do%20the%20necessary%20conversion%20to%20get%20the%20time%20aligned%20with%20the%20server's%20local%20timezone.%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F98263i01D633CF949F35AC%22%20%2F%3E%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20To%20summarize%2C%20we%20suggest%20the%20following%20steps%20when%20troubleshooting%20change%20tracking%20cleanup%20issues%3A%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%201.%20Ensure%20that%20auto%20cleanup%20is%20working%20properly%20using%20the%20Extended%20Event%20%22change_tracking_cleanup%22%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%202.%20If%20automatic%20cleanup%20is%20running%20slowly%2C%20then%20you%20can%20execute%20the%20stored%20procedure%20%22sp_flush_CT_internal_table_on_demand%22.%20In%20SQL%20Server%202014%20Service%20Pack%202%20and%20above%2C%20we%20provided%20a%20new%20Stored%20Procedure%2C%20sp_flush_CT_internal_table_on_demand%2C%20to%20assist%20with%20Change%20Tracking%20cleanup.%0A%20%20%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fkb%2F3173157%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%0A%20%20%20%20KB3173157%0A%20%20%20%3C%2FA%3E%0A%20%20%20has%20more%20details.%0A%20%20%3C%2FP%3E%0A%20%20%3CP%3E%0A%20%20%20Amit%20Banerjee%20(%0A%20%20%20%3CA%20href%3D%22http%3A%2F%2Ftwitter.com%2Fbanerjeeamit%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%0A%20%20%20%20%40banerjeeamit%0A%20%20%20%3C%2FA%3E%0A%20%20%20)%0A%20%20%3C%2FP%3E%0A%20%0A%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-385090%22%20slang%3D%22en-US%22%3EFirst%20published%20on%20MSDN%20on%20Jan%2017%2C%202017%20In%20the%20first%20part%20of%20my%20Change%20Tracking%20Cleanup%20post%2C%20I%20talked%20about%20how%20automatic%20and%20manual%20cleanup%20happens%20in%20SQL%20Server.%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-385090%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ESQLServerTiger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-995658%22%20slang%3D%22en-US%22%3ERe%3A%20Change%20Tracking%20Cleanup%20%26amp%3B%238211%3B%20Part%202%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-995658%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20database%20with%20change%20tracking%20enabled%20on%20two%20very%20active%20tables.%20Our%20database%20size%20keeps%20increasing%20and%20I%20have%20determined%20that%20the%20change%20tracking%20side%20tables%20are%20not%20getting%20flushed%20frequently%20enough.%20I%20started%20running%20sp_flush_CT_internal_table_on_demand%20against%20the%20two%20tables%20and%2C%20even%20with%20that%20running%20constantly%2C%20the%20side%20tables%20still%20grow.%20Our%20database%20is%20increasing%20by%20500%20MB%20every%2040%20minutes.%20The%20change%20tracking%20side%20tables%20currently%20have%2010.5%20billion%20and%202.5%20billion%20rows%20currently.%20I%E2%80%99ve%20had%20sp_flush_CT_internal_table_on_demand%20running%20against%20both%20tables%20for%203%20hours.%20In%20that%20time%2C%20it%20has%20deleted%2016.5%20million%20rows%20from%20one%20table%20and%20900K%20rows%20from%20the%20other.%20Even%20with%20those%20procs%20running%20constantly%2C%20the%20change%20tracking%20side%20tables%20are%20still%20growing%20by%20about%20250K%20and%204.5K%20rows%20per%20minute.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20running%20SQL%202016%20SP2%20CU8.%20We%20have%20change%20tracking%20enabled%20with%20track%20columns%20updated%20enabled%20as%20well.%20Do%20you%20have%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft
First published on MSDN on Jan 17, 2017

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 )

1 Comment
Occasional Visitor

We have a database with change tracking enabled on two very active tables. Our database size keeps increasing and I have determined that the change tracking side tables are not getting flushed frequently enough. I started running sp_flush_CT_internal_table_on_demand against the two tables and, even with that running constantly, the side tables still grow. Our database is increasing by 500 MB every 40 minutes. The change tracking side tables currently have 10.5 billion and 2.5 billion rows currently. I’ve had sp_flush_CT_internal_table_on_demand running against both tables for 3 hours. In that time, it has deleted 16.5 million rows from one table and 900K rows from the other. Even with those procs running constantly, the change tracking side tables are still growing by about 250K and 4.5K rows per minute.

 

I am running SQL 2016 SP2 CU8. We have change tracking enabled with track columns updated enabled as well. Do you have any suggestions?