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