I've been searching about the undocumented Trace Flag 8295.
It's been introduced with KB 2476322 in SQL Server 2008 and "'Creates a secondary index on the identifying columns on the change tracking side table at enable time'. This allows creation of a secondary index for changetracking tables, which we heavily use for replication between Configuration Manager sites. "
I have a SQL Server 2012 server and it has changetracking enabled on many tables. I've seen some SortWarnings go by on queries that tries to get informations via the "CHANGETABLE (tableName, tracking key)" function.
We usually join the CHANGETABLE informations with the underlying table via the primary key, but then select only a subset of the informations.
I was thinking about turning on the Trace Flag 8295 but I'm wondering if it's gonna do anything since I'm on SQL Server 2012 and not 2008?
How can I see the effects of it? I don't see any additional indexes on the change_tracking tables.
select OBJECT_NAME(ind.Id),OBJECT_NAME(i.parent_object_id),ind.* from sys.sysindexes ind join sys.internal_tables i on i.object_id=ind.id where i.name like 'change_tracking%'