1

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>
Improve this question
1

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • 1
    Thanks for sharing your experience. I just have one batch and stats are updated so it covers #1/#2. #3 In my case multiuser locking isn't the problem as there is only one process which is deadlocking itself. Anyway, snapshot isolation should allow writers to write while I read? Starts to feel like change tracking is not up to the job when I could just use a rowversion column on every table with an index on it and just maintain the index to keep fragmentation down. I think that is my next move.
    – Brett
    Commented Dec 23, 2020 at 21:07
  • Yeah change tracking has many limitations. You get better control by doing a row version column. We are switching to that ourselves.
    – Danielle Paquette-Harvey
    Commented Dec 23, 2020 at 22:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.