— by Lori Brown @SQLSupahStah
Once again, I find things about SQL Server that I had no idea were even available when I actually have time to do some in-depth reading and research. Most of the time, my regular duties keep me so busy that I don’t have time to play with new SQL functionality much so this makes it very easy to miss things like all the interesting trace flags that are available.
I found TF 4199 while doing some research on SQL 2016. Trace flag 4199 is used to turn on all hotfix or cumulative update (CU) functionality after it is installed. So, if you had an issue that should have been resolved by installing a CU or SP and did not find it happening, you could turn on trace flag 4199 to force all of the fixes to become active. Fixes and CUs are apparently not supposed to be turned on by default since they could affect execution plans. If you installed a specific hotfix, most of the time those come with their own trace flag that was needed to enable the fix. TF 4199 is the code to turn them all on.
To turn on a TF globally use the –T4199 command in your SQL startup parameters.
To turn on a TF for your session use DBCC TRACEON (4199).
TF 4199 can be enabled in a specific query by using the OPTION clause with QUERYTRACEON.
SELECT col1, col2, f4 FROM MyTable WHERE f1 = 0 AND f2 = 1 OPTION (QUERYTRACEON 4199)
In my defense, I have rarely installed hotfixes or CUs and have tended to install service packs and so far (knock on wood) to not have to use a trace flag to get expected benefits.
In SQL 2016, TF 4199 is enabled by default for databases in 130 compatibility.
For those of you who have not upgraded to SQL 2016, please be very cautious about enabling trace flags globally. Even with TF 4199, you can cause unexpected behavior to show up. If you do enable a trace flag, please have a good reason to do so. I have seen places (ahem** Microsoft ** ahem) that recommend TF 4199 be set globally but I would be very skeptical of doing this.
More info on trace flag 4199 can be found here: https://support.microsoft.com/en-us/kb/974006
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!