Methods for finding new Trace Flags in SQL Server

Question

There are a lot of Trace Flags out there. Some are well-documented, some are not, and others found their way to default behavior status in the 2016 release. Aside from official support channels, Microsoft employees, etc., what are ways to find new trace flags?

I've read through a couple recent posts by Aaron Bertrand here and here, but didn't spot anything about new Trace Flags.

I copied the data and log file of mssqlsystemresource to a new location, and attached it like a regular database to poke through system tables and views, but didn't spot anything immediately. I considered taking a list of known Trace Flags, and looping through numbers not on that list, to see which ones DBCC TRACEON would allow, but wanted to ask the question here first.

Assuming that the DBCC command to enable them has to check in with some resource to make sure the Trace Flag is valid, where does it reach out to? Is there a .dll or some other system file that holds a list?

I know the question casts a wide net, but what spurred this was reading about a Trace Flag with specific intended behavior alongside a new feature in 2016 that was not having the described effect. My initial thought was that perhaps the numbers were transposed somehow, like 7129 becoming 7219. I was hoping to get a list of valid trace flags within a range, say 7000-7999, to look for permutations. Testing them all, both as DBCC TRACEON flags and startup parameters would be quite a nuisance, combined with testing the results against the feature behavior.

Answer 1

There's nothing you can do to find the list except ask or pick them up from posts/slide decks/etc. The list only exists in the code, in a header file where the valid trace flag numbers are mapped to names in a big enum in the C++ code, and then the names are used in the rest of the code.

As Aaron said, you can enable any trace flag number, and if it doesn't do anything, or you don't exercise the functionality that the trace flag is relevant to, you won't notice any difference in behavior.

DBCC TRACEON doesn't check anything - as there's no run-time list of which numbers are valid or not - it just enables that trace flag number in a bitmap of what flags are set for that connection/globally.

The problem with having a validity check is that would expose which trace flags are valid, allowing them to be discovered. This way the 'valid list' is effectively obfuscated, which is what the SQL team wants.

Regarding Kin's suggestion in a comment that SQL Server should have select * from sys.available_trace_flags - Yes and no. There are many trace flags that are highly detrimental to performance and are only necessary for debugging problems under guidance from Product Support, but SQL Server could list the 'safe' flags.

Answer 2

What are ways to find new trace flags?

For the most part, it comes down to having the time and emotional resources to spend on looking for them.

Certainly, it is possible to write a script to loop through possible trace flag numbers and analyze the effects, but this is not always fruitful. There are many reasons for that, but common frustrations include the fact that some trace flags are only effective in combination with others, some only work with -T at startup, or when used with DBCC TRACEON, some only with OPTION (QUERYTRACEON). Some require undocumented commands, or command extensions, or for a particular feature to be enabled as well. Some only produce effects if you know where to look for those effects. And so on and ...very much... so on.

That said, perhaps the most effective technique is to step through the execution of a particular query or command step by step with a debugger or other profiling tool attached, comparing the paths taken with the trace flag(s) on and off. If this sounds time-consuming, that's because it is.

For me, something has to be potentially very interesting, or relate to a real-world problem without a better solution for me to even think about getting into it. It is also useful if you've been through this process hundreds or thousands of times before, to get a broad feeling for the sort of thing you're looking for, which range of trace flags is most likely to be effective, and which part of the codebase is going to be interesting.

Setting a breakpoint on CSessionTraceFlags::CheckSessionTraceInternal and checking the value of the edx register (to see which trace flag is being checked) can be useful in simple cases, but the interesting cases aren't often simple - and not all trace flags are checked at the point where they affect the code path taken.

In SQL Server 2019, set a breakpoint on sqllang!GetGlobalTraceFlagStore. When this very short function returns, the caller will put the trace flag number in register edx as above, before calling sqllang!get_bit to check if the flag is set.

There is a rather small list of official trace flags. These are the flags that have been fully tested and are (and will be) supported by CSS and, ultimately, the product developers. They are also flags with a common enough use case to be worth documenting.

Any other trace flag you find is a curiosity that might have unexpected effects in various situations (different builds, SKUs, security settings, different features...anything else you can or cannot think of). These will only be 'supported' by the person who wrote about them, if at all.

There are several unofficial lists, the best one I know of is A Topical Collection of SQL Server Flags by Aaron Morelli (currently at v6, April 2016).

All that said, Microsoft CSS do (ultimately) have access to all the trace flags, so they may be able to advise you on any you come across, even if they are not on the official list. They may choose not to say anything, of course, and there might be a fee involved; I really don't know, never having gone that route myself.

Answer 3

We maintain a collection of trace flags on github (now 599 trace flags):

Microsoft SQL Server Trace Flags

Also, very good technique described by Brent Ozar in his great article Bad Idea Jeans: Finding Undocumented Trace Flags and Joe Obbish in this awesome article A Method to Find Trace Flags