Risk of enabling change tracking

_{Tuesday, October 6, 2015 6:35 PM}

Hi,

We are developing an application that requires change tracking.

We tested it in development and test environments and we are preparing our production deployment.

The very first thing that needs to be done is an

ALTER DATABASE [db_name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.

Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?

Does anyone has experience, advice, best practices about enabling change tracking?

_{Thursday, October 8, 2015 5:47 AM ✅Answered | 1 vote}

Hi Fredito3,

There are some drawbacks of SQL Server Change Tracking, for example, automatic cleanup job sometimes creates blocking thus impacting user applications, slows down DML operations and so on. SQL Server Change Tracking is not a great fit for databases with a very high rate of transaction commits to the tracked tables.

If you persist to use Change Tracking in production environment, please review the following blogs about the impact of Change Tracking  in advance and perform the steps in blogs to make Change Tracking go faster.

Performance Tuning SQL Server Change Tracking
SQL Server Change Tracking Performance Troubleshooting

Thanks,
Lydia Zhang

Lydia Zhang
TechNet Community Support

_{Thursday, October 8, 2015 6:17 AM ✅Answered | 1 vote}

I think the issue is that when you alter database first it has to complete all transactions and flush all buffers.  If your dev system was fairly large and/or active, that would explain the time.  You can try running a checkpoint statement before the alter database and see if that helps.

As Lydia says if you have a very busy database this may not be your best move, have you run benchmarks of big transactions in your dev environment, with change_tracking on and off?  Are you monitoring transaction times and wait states so you can see what the effect is, in dev or production?

Josh

_{Thursday, October 8, 2015 3:03 PM}

Thanks Lydia for the references, they bring more useful information for our application.

I think we'll be ok once change tracking is enabled. We have one big and busy table but we didn't have the guts to use change tracking or other mechanism for it. Because we only needs a small subset of activities in this table, we manage the replication from code.

cheers!

_{Thursday, October 8, 2015 3:07 PM}

That might be it. We also have a DEV database running on a lower grade hardware so performance comes at a different price, though 4min is far from expected.

we'll be testing it