How to make sure change_tracking statistics stays updated

Question

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 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.

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(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 

Answer 1

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.

Answer 2

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