Change Tracking Automatic/Manual Cleanup Index

[JamesFerebee]

Howdy!

We often hear of autocleanup or manual cleanup issues regarding Change Tracking in CSS. If you have very busy tables that result in a lot of data being written to the side tables, you'll commonly see error 22122 in the SQL Server error log indicating there was a failure in running cleanup and we will retry later. 

2022-04-07 17:30:29.13 spid42s     Error: 22122, Severity: 16, State: 1.

2022-04-07 17:30:29.13 spid42s     Change Tracking autocleanup failed on side table of "SourceTableName".  If the failure persists, use sp_flush_CT_internal_table_on_demand to clean up expired records from its side table.

If the issue persists and we stack a lot of data that needs to be removed which can be too much for autocleanup to manage. At that point, we recommend running (as is indicated in the error message) sp_flush_CT_internal_table_on_demand which is discusseed in detailed in Amit's blog post Change Tracking Cleanup–Part 1.

I will not discuss the internals of the process as Amit Banerjee already touched on this in the referenced blog. However, if you are routinely encountering issues where autocleanup can't keep up or manual cleanup is taking significant time, you can add the index and see if it helps and I have the process enumerated below. Keep in mind this is not guaranteed to fix all cleanup issues and it may still be necessary to run manual cleanup regularly. If you continue to have issues with cleanup feel free to create a ticket with us here at CSS to assist you and get specific data to your environment.

How to Create si_commit_time Index

Some things to know before we start:

• If you have SQL 2019 or a more recent version of SQL, this index already exists. You do not need to do this process UNLESS your database was created prior to an upgrade of SQL 2019. When you upgrade SQL Server, we do NOT add the index to your user database as it can slow down the upgrade significantly. We create the index with the name si_commit_time_ts in the MODEL database, and so future databases created will then get the index.
• If you create this index manually and then upgrade to SQL 2019 or SQL 2022 in the MODEL database, you will have 2 indexes with the same columns. The index included with the product is si_commit_time_ts, the one we create here is si_commit_time. You'll want to delete the manually created one (si_commit_time) in model to reduce number of indexes and prevent query optimizer issues and so future databases created only have the built-in product index. The manual index we walk through below does not prevent upgrading and can be deleted later.
• We recommend you run the manual cleanup and letting it finish if at all possible BEFORE creating the index so the process is very fast. If you cannot, the question of how long the index will take to create is something we cannot answer due to the number of variables that impact the ability for SQL to create it. For reference, on one customer's server with 56 cores I saw it took approximately 11 minutes to create the index on a 26 GB syscommittab.
• You will have to stop and start SQL to go through this process, and we will be starting SQL in single user mode. This is an outage and needs to be approached as such.
• This is not a blanket recommendation to implement this for environments. However if you are having cleanup issues there is little to no risk to trying it. If you don't see performance improve, you can drop the index in the future.
• If your database is part of an Availability Group, you will not be able to add the index. You'll need to remove the database from the AG and follow the steps, then add back.
• The reason this index helps is an expensive part of cleanup is where we compare the invalid versions to syscommittab and pass a time parameter, which now we can use the NCI to do

How to do it:

1. First we want to do the manual cleanup using sp_flush_CT_internal_table_on_demand if we can to let it manually cleanup before proceeding. To help this run, if possible stop all updates against the CT enabled database. If you cannot, keep in mind the size of syscomittab is going to impact index creation as indicated above. The script in Amit's blog above is a great reference.
2. Launch SQL Server Configuration Manager
3. Then right-click the proper SQL Server service and go into Properties --> Startup Parameters tab and add under "specify a startup parameter" add  -mSQLCMD and -f (capitalization is important!) and restart the service. More information on the startup options are here; Database Engine Service Startup Options - SQL Server | Microsoft Docs
   1. Keep in mind if your instance is clustered, you'll need to edit these steps such as our documentation for clustered instances describes 
   2. 
4. Then launch sqlcmd on the SQL box through an administrative CMD and connect using the -A parameter to use the DAC Connection.
   1. HINT: If you press enter you can run SELECT @@SERVERNAME, press enter, then type go, and enter again to verify you're connected. 

   2. /*
         Example below is using Windows Authentication (-E parameter), for the default instance 
         on server WIN-L3UUVD9S3HV (-S parameter) and using DAC (-A parameter)
      */
      sqlcmd -E -S WIN-L3UUVD9S3HV -A

5. Then create the index using the code below. You'll want to do this for model (so newly created databases get this index) and any user database that has Change Tracking enabled. You can copy and paste this code after changing the database name into CMD and press enter and it should work. If it seems like it's taking a long time, verify that the database you are running against is recovered (check SQL Server error log for recovery messages). It should be very fast if you ran manual cleanup and syscomittab is small.

   1. USE [ChangeThisToYourDBName]
      GO
      CREATE NONCLUSTERED INDEX [si_commit_time]
      ON [sys].[syscommittab] (
          [commit_time] DESC,
          [commit_ts] DESC
      )
      WITH (SORT_IN_TEMPDB = ON)
      GO

6. You can then verify the index is created on each database by using the code below. A row returned means it was created. 0 means it did not.

   1. USE [ChangeThisToYourDBName]
      GO
      SELECT object_name(object_id) tablename,index_id,name as index_name,type_desc 
      FROM sys.indexes 
      where object_id=object_id('sys.syscommittab') and name = 'si_commit_time'
      GO

7. Then go into SQL Server Configuration manager, remove the -mSQLCMD and -f parameters and restart SQL. Now user connections should be able to access SQL and cleanup will use this new index.

Hope this helps!

James