5

Microsoft's KB article about Trace Flag 4199 is a bit confusing:

...any hotfix that could potentially affect the execution plan of a query must be controlled by a trace flag. Except for fixes to bugs that can cause incorrect results or corruption, these hotfixes are turned off by default, and a trace flag is required to enable the fix.

NOTE: I'm assuming that a "hotfix" is a Cumulative Update (aka "CU"). If I'm wrong, please leave a comment.

So...let's suppose I'm running the latest Service Pack. SP's include all of the fixes in previous CU's. Are the hotfixes turned on for SP's (or even RTM's)? Or would Trace Flag 4199 still be required?

Improve this question

2 Answers 2

Reset to default
5

In this context, you should consider "hotfix" to mean any fix that ended up in a Service Pack, Cumulative Update, or on-demand hotfix and falls under the jurisdiction of this trace flag. These fixes are all in the most recent builds of each major supported version, but they are not used unless the trace flag is turned on. This is because, in some cases, the "fixes" can actually lead to regressions (and worse performance) - and people shouldn't expect to have major variations in plan generation just because they applied an update or service pack.

I'm not sure if this is an exhaustive list, but this article lists many of the 41xx TFs that ultimately came under the 4199 umbrella. The v4 PDF made available in this blog post may contain more flags (I haven't cross-checked) but certainly contains more info on several of the individual fixes.

At some point in the future, I suspect that all of the enhancements from TF4199 will be enabled by default in a new full major version (but not in a service pack or CU). Perhaps they will provide a trace flag to turn them all off, just like you can turn off the new cardinality estimator.

Improve this answer
2
  • Thanks for the clarification, Aaron. Some of my co-workers in Dev-Ops spent some time at Redmond doing some load testing of our application. Microsoft gave us a laundry list of guidelines/best practices. One of those items was to enable Trace Flag 4199 at startup. To the best of my knowledge, we do not have any special requirements or business need for TF 4199. What are your thoughts?
    – Dave Mason
    Mar 27, 2015 at 14:13
  • @DMason I don't think it is one I would just turn on blindly unless I could demonstrate the need to do so. And even then if I had a handful of queries that benefit from those fixes I would probably first try to address those queries individually before just turning on all of these fixes for all queries.
    – Aaron Bertrand
    Mar 27, 2015 at 15:48
3

I work in the SQL Server Tiger team at Microsoft that's responsible for generating most of the hotfixes mentioned above. A hotfix is anything that changes the software after RTM (release to manufacturing), including CUs, Service Packs (which include all CUs released up to a few weeks before release of the Service Pack), GDRs (critical security fixes) and ODs (On Demand hotfixes).

Any fix that ends up under the 4199 umbrella should be enabled by default in a future version of SQL Server, under a future compatibility level, as they are expected to be improvements in the vast majority of cases. However, like anything where there are millions of users, there may be plan changes that benefit most people, but hurt a minority of users for one reason or another. Therefore, we ask users to explicitly enable any code change that can change plans using traceflag 4199, unless that change affects the correctness of results, security, or corruption.

One thing of note (that was missing in Aaron's answer above) is that for each new release we have a new compatibility level that, in a sense, resets the 4199 flag. Any fixes released by the time SQL Server 2016 was snapped for RTM should be present by default in 2016's compatibility level 130. If 4199 is not enabled, you can go back to 2014's behavior, good or bad, by changing the compatibility level of the database to 120. However, once 4199 is enabled on the server, you get all the fixes whatever the compatibility level happens to be. Any fixes after RTM was released will again be protected under 4199.

TL;DR: No plan changing fixes are enabled after RTM without enabling trace flag 4199, no matter the service pack or CU level. On the next RTM release, any existing 4199 fixes will be enabled by default, and the process starts over.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.