SQL Server Change Tracking

Solution

There are several solutions to the above scenario, such as Change Data Capture (CDC) and Change Tracking (CT) which are two widely used mechanisms. The focus of this tip is Change Tracking (CT). There are plenty of articles on the internet which describe how to use CT, so the focus here is not the implementation, but a discussion of practice and impact.

What Is SQL Server Change Tracking?

SQL Server 2008 introduced a feature called Change Tracking, MSDN claims it to be a lightweight process to capture only the changes that occur for tables. It also captures information about those changes. The main advantage of Change Tracking is that it does not require the development of custom solutions. Since Change Tracking is a built-in SQL Server feature, it is a more flexible and easy to use solution. However you need to use it with care to avoid system impact.

How Does SQL Server Change Tracking Work?

After you enable CT, it is able to capture records for each change to each row in a user table, a row is then added to the internal table (sys.change_tracking_*) also called side tables. The internal tables are created automatically when you enable CT for a user table. Each user table enabled for CT has its own internal table which stores data changes and other information. To query the CT data you use CT functions. Stay tuned for more information about querying CT data.

What's the Overhead of SQL Server Change Tracking?

It is vital to understand the overhead of CT before implementing. Several issues have been reported with CT especially in highly transactional systems. Any additional feature you implement in SQL Server has a cost, nothing comes free. For CT, since it captures the primary key and some other information for each row change, it too has a cost. Mainly it can be broken down as shown below:

1. Each row captured in CT has a small fixed overhead plus a variable cost equal to the size of the primary key columns. If column tracking is enabled, that also has a cost, each changed column requires an additional 4 bytes. Higher the number of columns, higher the overhead.
2. For each transaction, a row is added to the internal table. MSDN states this cost is similar to having an index for a table. As a result transaction time increases thus it affects response time.

Commonly Used SQL Server Change Tracking Commands

The below shows commonly used commands in CT and the impact to production systems when executing.

Enable Change Tracking at the database level

No impact, but be careful when choosing the retention period. Retention period is there for recovery purposes. You would set it based on the longest you believe something could go wrong. I would set it as few as 12 hours or up to 7 days. Retention period and auto cleanup settings can be changed at any time after CT is enabled.

ALTER DATABASE AdventureWorks2008R2
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

Change Change Tracking retention period

Potential impact. The longer you keep the CT data the larger it stays in the CT internal tables which may cause potential performance issues due to blocking when the auto cleanup process kicks off. The cleanup process has to parse through the data to discover which records to delete based on the retention period, so the more records, the longer the cleanup process takes.

ALTER DATABASE AdventureWorks2008R2 
SET CHANGE_TRACKING (CHANGE_RETENTION = 5 DAYS)

Disable Change Tracking Auto Cleanup internal process

Potential impact. At any time if you decided to disable CT auto cleanup process due to performance reasons, you are safe to do so with no impact. However by doing this, the internal CT tables will just keep growing unchecked which may impact performance issues later on.

ALTER DATABASE AdventureWorks2008R2 
SET CHANGE_TRACKING (AUTO_CLEANUP = OFF)

Enable Change Tracking Auto Cleanup internal process

Potential impact. You can enable the CT auto cleanup process (if it is already disabled) at any time. However by doing this, the auto cleanup process will activate internally as a background process and it will remove the old CT information. This process sometimes causes blocking if the CT tables are also very busy from user activity. The behavior of the cleanup process will be discussed later in this tip.

ALTER DATABASE AdventureWorks2008R2 
SET CHANGE_TRACKING (AUTO_CLEANUP = ON)

Disable Change Tracking at database level

No impact. This is the last statement you execute to disable CT. You need to disable CT at the table level first and then you do so at the database level.

ALTER DATABASE AdventureWorks2008R2 
SET CHANGE_TRACKING = OFF

Enable Change Tracking at table level with column track ON

No impact. This example will create an internal table to track changes of the Person.Person user table. Track_Columns_Updated setting lets you track the columns which have changed. Setting this value to ON has extra storage overhead and querying overhead.

ALTER TABLE Person.Person
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Disable Change Tracking at table level

No impact. When you disable CT, this triggers the dropping of the corresponding internal table as well. However it is advisable to do so after hours or during low usage time especially for internal tables that have millions of records.

ALTER TABLE Sales.SalesOrderDetail 
DISABLE CHANGE_TRACKING

How SQL Server Change Tracking Auto Cleanup Works

The cleanup should happen for the following tables:

1. sys.syscommittab table (main table). This is a system table and exists in each database all the time regardless of the CT status. This table is used by CT, but not limited to CT.
2. sys.change_tracking_*. These tables are limited to CT and each user table where CT is enabled has its own side table. (Note: This is not a DMV.)

Figure 1 shows the CT internal tables (main table and side tables) as mentioned in #1 and #2 above.

Figure 1

If the Auto Cleanup is disabled, you will notice both the main table and side tables keep growing depending on the activities against the user tables. If the Auto Cleanup is enabled, it removes the data from the main table and side tables as mentioned below.

For side tables - the cleanup task occurs in the background every 30 minutes in batches. I have noticed a batch of 5000 record deletes at a time in SQL Server 2012. See the below command:

delete top(@batch_size) from sys.[change_tracking_1762821342] 
where sys_change_xdes_id in 
(select xdes_id from sys.syscommittab ssct where ssct.commit_ts <= @csn)

For main table - it is presumed to be cleared at checkpoints. Even after disabling CT at the database level, this table clears slowly with the rate of 5 to 6 million records per day. This is I observed in SQL Server 2012.

Execution of the Auto Cleanup process can be monitored using Extended Events.

select * from sys.dm_xe_objects 
where name ='syscommittab_cleanup' 
or name ='change_tracking_cleanup'

Is Manual SQL Server Change Tracking Cleanup Possible

Yes. There is a system stored procedure available (sys.sp_flush_commit_table_on_demand) in case we want to do a manual cleanup with configurable batch size. However it should be used only in cases where you can not manage the CT internal tables with auto cleanup. You should also disable Auto Cleanup whenever you run manual cleanup otherwise they may block each other.

EXEC sp_flush_commit_table_on_demand 100000

What Is Good About SQL Server Change Tracking

• Very light weight mechanism
• Easy to configure
• SQL Server has more control than user
• Able to capture the primary key and some more information for INSERT/UPDATE/DELETE operations
• Able to track column changes using TRACK_COLUMNS_UPDATED ON setting
• Synchronous. It happens as part of the transaction
• Does not impact replication

What Is Not So Good About SQL Server Change Tracking

• User has less control as this is more of an integrated mechanism
• Automatic Cleanup job sometimes creates blocking thus impacting user applications
• Should be very cautions when implementing in highly transactional OLTP system
• Slows down DML operations as CT is part of the transaction

Common Issues with SQL Server Change Tracking

Backup failed after enabling CT. Read more here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/59f1f378-e9aa-48c8-84cc-f1542a926ebe/backup-failed-after-activating-change-tracking?forum=sqldisasterrecovery

Change tracking cleanup does not cleanup the sys.syscommittab system table in SQL Server 2008. This was a bug and it has fix, but you can read more here: http://support.microsoft.com/kb/973696?wa=wsignin1.0

How To Troubleshoot SQL Server Change Tracking

After enabling CT, it has to be monitored in case of issues. There are a few system catalogs that expose the details of CT and the extended events also can be used to troubleshoot CT related issues.

Following are list of system catalogs which you can use to monitor CT.

select * from sys.change_tracking_databases

This returns one row for each database that has CT enabled. Figure 2 shows the sample output.

Figure 2

select * from sys.change_tracking_tables

This returns one row for each table in the current database that has CT enabled. Sample output is shown in Figure 3.

Figure 3

select * from sys.internal_tables
where internal_type_desc='CHANGE_TRACKING'

This returns one row for each table that SQL Server created internally for various features such as CT. Using the WHERE clause stated above it returns only the internal tables created for CT.

Figure 4

The change_tracking_* table(s) are internal. When you enable a table for CT, SQL Server creates this internal table to track and store the changes. You can use the below query to see all the CT internal tables and the corresponding base table.

select sch.name, it.name as CT_Name, ps.row_count as CT_Rows, 
sch2.name as Base_TableSchemaName, so2.name as Base_TableName
from sys.internal_tables it
inner join sys.objects so on it.object_id=so.object_id
inner join sys.schemas sch on sch.schema_id=so.schema_id
inner join sys.dm_db_partition_stats ps on ps.object_id=it.object_id
left join sys.objects so2 on so2.object_id=it.parent_object_id
left join sys.schemas sch2 on sch2.schema_id=so2.schema_id
where it.internal_type IN (209, 210)
and ps.index_id < 2  

Figure 5

Things to Keep In Mind with SQL Server Change Tracking

• CT must be enabled for each table that you want to track changes.
• When setting the retention period, consider how often applications will synchronize with the tables in the database. Setting this to a higher value in a busy OLTP system might lead to performance issues because the background cleanup job will execute periodically to remove the old CT information after reaching the retention period. I would suggest to keep the retention period to several hours or few days for busy OLTP system.
• CT is designed to use snapshot isolation level to achieve information consistency. However enabling snapshot isolation level has other disadvantages like excessive use of tempdb. As a result you need to evaluate the cost benefits and tradeoffs of such a change.
• Database compatibility level must be set to 90 or greater to use all CT features.
• There is overhead when enabling CT for DML operations. As per MSDN, CT has been optimized to minimize the performance overhead. It is very similar to the overhead when an index is created for a table and needs to be maintained.
• CT internal tables are not accessible via normal connections, meaning a SELECT will not work against the internal tables. You need to run a SELECT for internal tables using the DAC (Dedicated Admin Connection) connection.

Conclusion

Change Tracking is an easy and quick solution to implement, but sometimes creates issues depending on how busy your system is. Another use of CT will be to use it against a replicated database, so that it will not impact the primary OLTP system. This is very good design pattern if your concern is performance of the OLTP database, however the downside is there will be a latency due to replication.