Change Tracking May Leads to Blocking and Dead Locks

Changing Tracking: May not be a viable solution on OLTP databases.

CT (change tracking) primarily used for Synchronizing the data.

As per Books Online: It tracks what rows are changed. The latest data can be obtained directly from the table that is being tracked. It’s a light weight method to track the changes, unlike triggers which needs programming with additional tables and cleanup process."

But it’s not light weight in terms of the problems raised with blocking.

Change tracking itself creates few internal tables without having any control over them to maintain or troubleshoot during the issues raised with performance.

In this scenario, we wants to synchronize the tables.

Steps involved in synchronize 2 tables using change tracking are

1.     Populate the Target table with initial full set of data from the source table

2.     Identify the changes on the source tables using change tracking

3.     Synchronize both the tables with the changes (insert,update,delete) happens on the source table to the destination table

There are many methods available viz..Triggers, Time Stamp Tracking, Log shipping, Replication, Mirroring (with snapshot) etc. and each method has its own limitations

But if the solution has using change tracking then beware of the problem with deadlocks.

"A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

Image: Dead Lock Trace Captured from Profiler

Steps:

1.     Create a Test Database

2.     Create a test table with test data

3.     Enable Change Tracking on database

4.     Enable Change Tracking on Table

5.     Run Update Statements

6.     Catch Deadlocks

7.     Conclusion

8.     Alternate solutions

1.     Create a Test Database

Create database TestDB Go Use TestDB Go

2.     Create a test table with test data

USE TestDB GO create table [Customer](     IID INT IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED, [CustomerID] [int] NOT NULL , [PersonID] [int] NULL, [StoreID] [int] NULL, [TerritoryID] [int] NULL, [AccountNumber]  varchar(30), [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL, [ModifiedDate] [datetime] NOT NULL) GO insert into [Customer] SELECT * FROM AdventureWorks2008.Sales.Customer

3.     Enable Change Tracking on database

ALTER DATABASE TESTDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

4.     Enable Change Tracking on Table

USE TestDB GO ALTER TABLE customer  enable CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = Off);

5.      

6.     Run Update Statements

-- open a new session(1) and run the below statement while 1=1 BEGIN insert into [Customer] SELECT * FROM AdventureWorks2008.Sales.Customer END; -- open a 2nd new session(2) and run the below statement while 1=1 BEGIN     update Customer set PersonID =customerid END;

7.     Catch Deadlocks

select XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as DeadlockGraph FROM (select CAST(target_data as xml) as TargetData from sys.dm_xe_session_targets st join sys.dm_xe_sessions s on s.address = st.event_session_address where name = 'system_health' and target_name = 'ring_buffer') AS Data CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent) where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

8.     Conclusion:

make sure before implementing change tracking on the tables with high volume of concurrent insert and updates. Change tracking creates few internal tables which are the main sources of causing these deadlocks.
During every modification on source table , it creates one record in the internal change tracking table. So , If Transaction A inserting records every 1 second and at the same time Transaction B is updating the records than the concurrent blocking will be placed on the Internal tables between these 2 transactions which leads to deadlocks.

Choose this option wisely or write your own custom code using other features where you can take full control.
The major challenge involved in synchronizing the tables are identifying the modifications (updates) than the inserts/deletes as inserts/deletes can be identified by using an anchor table by comparing the primary keys in any data warehouse environment.

Major challenge here is identify the modifications, we have other options mentioned below to identify the modified rows where each has its own limitations. Choose your options wisely

i)       Binary checksum

ii)      Timestamp

iii)     Non-key attributes with in the table to keep track of the changes by the application like dt_modified

iv)    Triggers

v)     CDC – change data capture

vi)    CT – change tracking

vii)   T-Replication

viii)  3^rd party tools

Also refer the article written by Brentozar at the below link. His article pretty much covered about change tracking.

http://www.brentozar.com/archive/2014/06/performance-tuning-sql-server-change-tracking/