I am using Microsoft SQL Server 2016 (SP2-GDR) (KB4505220) - 13.0.5101.9 (X64) Jun 15 2019 23:15:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )

I have multiple databases with CHANGE_TRACKING enabled on high insert/update tables. My retention period is 2 DAYS, with auto cleanup enabled. Database compatibility is set to 130 (SQL 2016)

change tracking config

Some of the tables tracked are huge and can have thousands of insert/update every day. This is the content of the change_tracking tables.

change tracking tables

Here is an example of query we do to get the changes.

SELECT Columns
INNER JOIN MyTable b ON b.Key=CT.Key
WHERE b.Status = 1

I have seen from time to time that the queries to get the latest changes on some of the tables take an awful long time to complete and generates a lot of CPU. To help with this, I've setup a daily update statistics on change tracking tables that run every night. And it helps a lot, but sometimes, during high user activity days, I have to run this update statistics even during the day. When I run the update statistics on those table, the situation is back to normal and the queries to get the latest changes run fine for some time.

We use the change tracking for some vital parts of our applications, so it has to work.

Is there any option, trace flag that I can enable to help with change tracking statistics? Anyone have experience with change tracking on high activity databases can give me some advice?

So I finally decided to do a job to check if change tracking table has changed more than 20k rows since last statistic update, then the job will update the statistics on the change tracking table.

I'll put the query here if it can help someone else. This query gives you all the statistics for change tracking tables where the table changed more than 20k times since last update statistics. It gives you a "Update Statistics" with the object_name to update. You just need to run results from the query on your database. I'll fine tune it for my workload and see if 20k is the best number.

 -- 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(50),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 > 20000 

Since you are on SQL Server 2016 with DB compat mode, the behavior of TF 2371 is the default behavior.

You no longer have to enable trace flag 2371 in SQL Server 2016 or later because the corresponding behavior is enabled by default.

You should enable auto update async database option so that when sql server updates the stats, it does not affect your workload. Changing this db option is an online operation.

What you can do is to leverage sys.dm_db_stats_properties - new DMV to decide if you must manually update statistics for a table. You can have a sql agent job configured to check and update stats every x min or hour for certain key tables.

  • About the auto update async, Brent Ozar recommends setting it off. brentozar.com/blitz/auto-update-stats-async-enabled What do you think about that? I already have "auto update" setting on. But I never tried the auto update async – Danielle Paquette-Harvey Nov 1 '19 at 15:41
  • 2
    Brent does not recommend setting it OFF as blanket rule - We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems.. I have currently enabled this option and it is helping us a lot. This basically kicks off update stats in an async fashion which for volatile tables is beneficial. As always, test this out in non-prod environment. – Kin Shah Nov 1 '19 at 15:50
  • I'll mark your answer as the answer. I'm going to setup a job to update stats every x min for tables that have changed a lot since the last update statistics. I'll edit my question to add the query in case it can help someone – Danielle Paquette-Harvey Nov 1 '19 at 18:05

You might look into this. Kendra did a good write up on change tracking performance, and it's helped us as well.

  • 1
    Yes thanks, I already did read her blog. This is where I get my queries to monitor my change tracking tables :-) – Danielle Paquette-Harvey Nov 1 '19 at 15:35
  • 5
    Link only answers aren't great for the SE format - could you potentially pick out the relevant text and quote in your answer? – George.Palacios Nov 1 '19 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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