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.