Improvement: General improvements to the change tracking cleanup process in SQL Server 2016 and 2017

Applies to: SQL Server 2017 Developer on WindowsSQL Server 2017 Enterprise on WindowsSQL Server 2017 Enterprise Core on Windows

Summary


This update contains the following improvements to the change tracking cleanup feature in Microsoft SQL Server 2016 and 2017 on Windows.

  • The cleanup of the syscommittab table at a hard checkpoint is avoided, because this might have impact on the performance of checkpoint operations. Instead, the syscommittab table may be cleaned as soon as the change tracking auto cleanup is completed. When the change tracking auto cleanup is completed, it updates watermark version – hardened cleanup version. This hardened cleanup version is used in the syscommittab cleanup to identify the records to be removed.
  • Currently, in the change tracking auto cleanup process, whenever the cleanup thread experiences blocking while deleting or cleaning rows from a side table, the auto cleanup stops and exits from the process. With this update, instead of exiting from the process, the problematic tables are marked for retries and proceed with remaining tables in the list. When the thread finishes cleaning up the final table, just before it updates the hardened cleanup version, the process will be retried to check if there are any tables that are marked for retries.
  • Additionally, the following sub events are added to the existing change_tracking_cleanup XEvent.

a) ErrorInTableCleanup – This helps identify if the change tracking cleanup experiences any issues when it cleans up expired entries from any of the side table.

b) RowsCleanedupInLoop – This helps keep track of the change tracking auto cleanup progress at a table level.

c) RetryCleanup – This helps identify the retries that are made by the change tracking auto cleanup thread.

Resolution


This update is included in the following cumulative update and service pack for SQL Server:

       Cumulative Update 2 for SQL Server 2017

Service pack information for SQL Server 2016

       Service Pack 2 for SQL Server 2016

References


Learn about the terminology that Microsoft uses to describe software updates.