How to make sure change_tracking statistics stays updated

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)

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.

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

SELECT Columns
FROM CHANGETABLE(CHANGES MyTable, @TrackingKey) AS CT 
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 objec

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.

StackExchange Database Administrators Q#252377, answer score: 4