Trace flag 4199 in SQL Server

What is often unknown about service packs and hotfixes

Starting with MSSQL 2000 Service Pack 3, Microsoft has decided that any modification in their software that can affect the execution plan of a query must be specifically enabled. By default, all improvements in an update, hotfix or service pack are disabled!

The exception to this are bug fixes due to an erroneous or unreliable result or to components that could lead to corruption. These are enabled by default. This policy prevents queries from suddenly starting to behave differently (more negatively) after installing an update.

The option to activate the improvements is done with a traceflag. A traceflag is a number that influences the behavior of MSSQL Server. You can compare a trace flag with the old DIP switches that you sometimes come across in equipment. To activate the mentioned improvements in a hotfix or service pack, traceflag 4199 must be activated.

This method of Microsoft is understandable; if you have very specific queries that can only function properly with a specific execution plan, you don’t want this to be negated by an update. But in practice you rarely come across this situation and the performance gain by activating this traceflag yields much more than the possible chance of suddenly malfunctioning queries.

A good example is Service Pack 1 of MSSQL 2014. MSSQL 2014 has a new Query Optimizer and this comes with a lot of bugs in the RTM version. Many of these bugs have been fixed in SP1, but since these bugs did not lead to corruption, the improvements are not enabled. So if you use MSSQL 2014 with SP1 with the new Query Optimizer, you cannot avoid activating this trace flag. Another example is the Dynamics application, of course from Microsoft itself. The development team of this application has prepared a list of traceflags that should be activated for better performance. Traceflag 4199 is one of them.

Note that there are traceflags, which are described by Microsoft (“documented”) and thus supported and that there are known “undocumented” traceflags. The use of undocumented traceflags carries a risk, as this can lead to a situation where support is no longer provided. Conversely, undocumented traceflags may only be activated on the advice of Microsoft.

Always study the purpose and effects of a traceflag before trying it out in a test environment. We are happy to offer you support with the application of traceflags if you wish. At the moment there are already more than 100 traceflags and this number is only increasing.