2

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%'
  • 2
    It seems like the bug was fixed after 2008 R2, and I'm not sure that it addresses a sort warning specifically anyway. – Aaron Bertrand May 4 '16 at 22:25
  • Well I know it doesn't adresses sort warning specifically. The trace flag is still available even in SQL Server 2012. – Danielle Paquette-Harvey May 5 '16 at 12:38
  • 2
    What does "available" mean? You can say DBCC TRACEON(any number) and it won't return an error. That doesn't mean it does anything. Several trace flags have been rendered as no-ops. – Aaron Bertrand May 5 '16 at 13:19
  • Well I guess what I mean is : I want to know if it's gonna have any effect if I turn it on. I did turn it ON on a test Server but I can't generate enough stress on it to see if it has an effect. So I wanted to know if anyone had experience with it before deciding if I try it on the production server or not. – Danielle Paquette-Harvey May 5 '16 at 13:52
  • I did write to Paul S. Randal and he said "Yes, I know about this trace flag and it's been discussed on the MVP distribution list with people from Microsoft. Upshot is that they wouldn't disclose everything that it does, but we were fairly certain that it just created the index and so is safe. I can't tell you 100% though as I haven't personally used it, so I don't know what it's going to do on other versions." – Danielle Paquette-Harvey May 5 '16 at 13:52

Your Answer

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

Browse other questions tagged or ask your own question.