Sql-server – intra-query parallel thread deadlocks with only Page Locks with snapshot isolation and change tracking

change-trackingdeadlocksnapshot-isolationsql serversql-server-2016

I've been trying to optimise a data loading process. This is on Sqlserver 2016 (13.0.5850.14 – latest SP&CU). I've switched to change tracking for internal database changes and set tablocks on all my inserts. As change tracking recommends I've enabled snapshot isolation.

I'm now getting self-deadlocks. Originally I was getting some of the traditional Intra-Query Parallel Thread Deadlocks, so I created some cache tables as standard user tables with indexes and pre-populated them to simplify the plans significantly. Now I am getting a different kind of self-deadlock. These only have Page Locks in the TempDb (not an exchangeEvent in sight) which makes me think that something else is going on. It is now a pretty simple query left joining to a bunch of tables to lookup Keys and applying a where in clause on changetable(changes, …) to only get those changed rows.

It doesn't deadlock if I either remove the where clause on changetable(changes, …) (so my query is no longer honouring the change tracking) or if I don't run in a "set transaction isolation level snapshot" transaction. But change tracking explicitly says it is needed. I know I could set DOP to 1 but it does make a massive difference to the execution time.

I'm considering forgetting changetable and going back to having an indexed timestamp column for change tracking.

Interested in any other ideas?

here is the graph

deadlock graph

<deadlock>
 <victim-list>
  <victimProcess id="processe3486d0ca8" />
 </victim-list>
 <process-list>
  <process id="processe3486d0ca8" taskpriority="0" logused="20000" waittime="4255" schedulerid="2" kpid="11556" status="suspended" spid="86" sbid="0" ecid="13" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe348241848" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0xe5f02d3ba0" lockMode="S" schedulerid="8" kpid="16044" status="suspended" spid="86" sbid="0" ecid="11" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe34820b848" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0xe5fdd5dcb0" lockMode="S" schedulerid="1" kpid="9460" status="suspended" spid="86" sbid="0" ecid="16" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe3486d3468" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0x106a5a07ce0" lockMode="S" schedulerid="7" kpid="9868" status="suspended" spid="86" sbid="0" ecid="12" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe6d5137848" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0xe95493e960" lockMode="S" schedulerid="6" kpid="19216" status="suspended" spid="86" sbid="0" ecid="10" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe348cbb088" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0xec5ec16960" lockMode="S" schedulerid="3" kpid="13816" status="suspended" spid="86" sbid="0" ecid="14" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="processe97dff0ca8" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0x10691260df0" lockMode="S" schedulerid="5" kpid="11736" status="suspended" spid="86" sbid="0" ecid="15" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
  <process id="process106d192c108" taskpriority="0" logused="14148276" waitresource="PAGE: 2:3:27824 " waittime="10657" ownerId="510106180" transactionname="user_transaction" lasttranstarted="2020-12-23T13:52:13.520" XDES="0xef0a3d9c00" lockMode="S" schedulerid="4" kpid="17492" status="suspended" spid="86" sbid="0" ecid="9" priority="0" trancount="0" lastbatchstarted="2020-12-23T13:52:13.520" lastbatchcompleted="2020-12-23T13:52:13.380" lastattention="1900-01-01T00:00:00.380" clientapp="Microsoft SQL Server Data Tools, T-SQL Editor" hostname="BV002571" hostpid="15328" isolationlevel="snapshot (5)" xactid="510106180" currentdb="17" currentdbname="ValueRetailDW_41975" lockTimeout="4294967295" clientoption1="673187936" clientoption2="390200">
   <executionStack>
    <frame procname="ValueRetailDW_41975.star.LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS" line="72" stmtstart="7052" stmtend="8260" sqlhandle="0x030011003de9c87a648ddd009aac000001000000000000000000000000000000000000000000000000000000">
select 
        Transaction_id, TransactionDateKey, BarcodeNumber, CurrencyKey, VisitRecordKey, MarketingActivityKey, GroupBookingKey, BookingTypeKey, MarketingPartnerKey, Time, VillageKey, BrandKey, LeaseKey, CustomerKey, LocalisationKey, OfferKey, GeographyKey, eVIPTier, BarcodeMetadataSource, GrossBrandSalesTracked, TaxAmount, _AuditKey, _LoadTimestamp, _RecordSource
    into #toload -- must insert into a temp table (not variable) to get parallel capability of the complex query
    from [map].[DI_BIExport_TrackedSales_DS_TO_LOAD_FactTrackedSales] (@LastAnchor)         
    option (recompile    </frame>
    <frame procname="adhoc" line="1" stmtend="134" sqlhandle="0x01001100d905560c90db0fcae700000000000000000000000000000000000000000000000000000000000000">
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS    </frame>
   </executionStack>
   <inputbuf>
exec [star].[LOAD_FactTrackedSales_FROM_DI_BIExport_TrackedSales_DS] -- normal 
   </inputbuf>
  </process>
 </process-list>
 <resource-list>
  <SyncPoint id="SyncPointef0598ed00" logicalOperatorType="Left Outer Join" physicalOperatorType="Hash Match" WaitType="HTMEMO" nodeId="8" synchronizingAt="ReadyForHashTableMemoColumn" ownerActivity="NotYetArrived" waiterActivity="Arrived">
   <owner-list>
    <owner id="process106d192c108" />
    <owner id="processe6d5137848" />
    <owner id="processe348241848" />
    <owner id="processe34820b848" />
    <owner id="processe3486d3468" />
    <owner id="processe348cbb088" />
    <owner id="processe97dff0ca8" />
   </owner-list>
   <waiter-list>
    <waiter id="processe3486d0ca8" />
   </waiter-list>
  </SyncPoint>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="processe348241848" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="processe34820b848" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="processe3486d3468" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="processe6d5137848" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="processe348cbb088" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe3486d0ca8" mode="X" />
   </owner-list>
   <waiter-list>
    <waiter id="processe97dff0ca8" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
  <pagelock fileid="3" pageid="27824" dbid="2" subresource="FULL" id="lockee0a16d800" mode="X">
   <owner-list>
    <owner id="processe97dff0ca8" mode="S" requestType="wait" />
   </owner-list>
   <waiter-list>
    <waiter id="process106d192c108" mode="S" requestType="wait" />
   </waiter-list>
  </pagelock>
 </resource-list>
</deadlock>

Best Answer

I've been having deadlock problems for years on change_tracking tables. Here is a few things that I've done to reduce them.

#1 : Check the size of your change_tacking tables and your retention period

The bigger those tables get, the more likely you'll get deadlocks while querying them. Here is a query to get the size of the change_tracking tables.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

select sct1.name as CT_schema,
sot1.name as CT_table,
ps1.row_count as CT_rows,
ps1.reserved_page_count*8./1024. as CT_reserved_MB,
sct2.name as tracked_schema,
sot2.name as tracked_name,
ps2.row_count as tracked_rows,
ps2.reserved_page_count*8./1024. as tracked_base_table_MB,
change_tracking_min_valid_version(sot2.object_id) as min_valid_version
FROM sys.internal_tables it
JOIN sys.objects sot1 on it.object_id=sot1.object_id
JOIN sys.schemas AS sct1 on
sot1.schema_id=sct1.schema_id
JOIN sys.dm_db_partition_stats ps1 on
it.object_id = ps1. object_id
and ps1.index_id in (0,1)
LEFT JOIN sys.objects sot2 on it.parent_object_id=sot2.object_id
LEFT JOIN sys.schemas AS sct2 on
sot2.schema_id=sct2.schema_id
LEFT JOIN sys.dm_db_partition_stats ps2 on
sot2.object_id = ps2. object_id
and ps2.index_id in (0,1)
WHERE it.internal_type IN (209, 210);
GO

#2 : Make sure your statistics are updated on the change tracking tables

I have some change_tracking tables that get so big that from time to time during the day statistics get outdated and I get terrible plan cache on those tables. I've setup a job that updates statistics when more than 100k changes has occurred in my tables.

Here is the query I run into a job every hour.

SET NOCOUNT ON

DECLARE @Query NVARCHAR(MAX), @DatabaseName NVARCHAR(MAX)

DECLARE database_cursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT name from sys.databases where  state=0

OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0 
BEGIN 

  SET @Query = 'USE [' +   @DatabaseName + ']; 

DECLARE @Query nvarchar(max)

DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR
SELECT 
 -- st.object_id                          AS [Table ID]
--, OBJECT_NAME(st.object_id)             AS [Table Name]
--, st.name                               AS [Index Name]
--, STATS_DATE(st.object_id, st.stats_id) AS [LastUpdated]
--, modification_counter                  AS [Rows Modified]
''UPDATE STATISTICS sys.['' + OBJECT_NAME(st.object_id) + '']'' as QueryToRun
FROM
sys.stats st 
CROSS APPLY
sys.dm_db_stats_properties(st.object_id, st.stats_id) AS sp 
WHERE OBJECT_NAME(st.object_id) like ''change_t%''
AND OBJECT_NAME(st.object_id) in (SELECT  ''change_tracking_'' + convert(nvarchar(255),st.object_id) FROM sys.change_tracking_tables ctt inner join sys.tables st ON st.object_id = ctt.object_id inner join sys.schemas ss ON ss.schema_id =     st.schema_id)
AND modification_counter > 100000 

OPEN cur

FETCH NEXT FROM cur INTO @Query
WHILE @@FETCH_STATUS = 0
BEGIN
  exec sp_ExecuteSql @Query

  FETCH NEXT FROM cur INTO @Query
END

CLOSE cur
DEALLOCATE cur  
'
  exec sp_executeSql @Query

FETCH NEXT FROM database_cursor INTO @DatabaseName
END

CLOSE database_cursor 
DEALLOCATE database_cursor

#3 : Make sure the queries on the change tracking table are as efficient as possible

When you query the change tracking table by tracking key, SQL will try to lock the table in order to avoid other queries from modifying it and thus changing more stuff.

I've been querying the change tracking tables, putting it into temp tables and then joining on my other tables.

Other stuff

On my system, we had thousand of devices trying to query the change tracking tables at the same time. We reduced deadlocks by implementing other ways to synchronize part of the data (like a "LastUpdated" column instead of relying on change tracking), and implementing a central cache that gets refreshed via SQL Broker. Change tracking is great but it does have limitations.

Related Question