SQL Server – Understanding Trace Flag 4199

execution-plansql server

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?

Best Answer

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.

Related Solutions

SQL Server – Best Practices for Cumulative Updates

I am a big advocate of keeping up to the date with the most recent cumulative update, but only if your testing/QA cycle can ensure full and proper regression testing against it. Glenn Berry of SQLskills is also a proponent of this approach.

Microsoft's own recommendation is to only apply CUs that fix issues that affect you, though they have loosened that stance recently. The problem is that you may be affected by one or more of those issues and not know it, or you might be affected tomorrow even if it hasn't hit you yet. Are you going to go through and try to reproduce the issue behind every single fix in every single CU for your branch? Are you going to do this continuously to make sure you still aren't affected?

I'll be quite honest: I've never had an issue applying any CU to my instances. And in fact their CU release process has been a lot more reliable than the service pack release cycle, and in many cases (including most recently with SQL Server 2012 Service Pack 2), you don't want to apply the service pack until the first CU for that branch has been released anyway. In this case there is an interim hotfix to resolve the issue that wasn't fixed in time to make the Service Pack code, but that isn't always true.

Sql-server – Trace Flag 1222 Not Working

I had a similar issue, not sure it will sort yours.

Try this:

EXEC master..sp_altermessage 1205, 'WITH_LOG', TRUE;
GO

Even though it was logging in the event log via the trace flag, this also needs to set in order to trigger the emails. You can see the table here:

select * from master.sys.messages
where text like '%deadlock%'

You can have more details here

Best Answer

Related Solutions

SQL Server – Best Practices for Cumulative Updates

Sql-server – Trace Flag 1222 Not Working

Related Question