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


image


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'

image




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)  3rd 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/


Post a Comment