One Trace Flag to rule them all!

As you know, Microsoft updates SQL Server continously. The downside to that is of course that it makes it hard to keep track of all fixes and improvements. And when Microsoft makes changes to the Query Optimizer, that can significantly impact the perfomance of your applications, mostly in good ways, but sometimes it can also negatively afffect the performance of your particular application. Therefore, starting with Microsoft SQL Server 2000 Service Pack 3 (SP3), Microsoft adopted a policy that 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. This policy change helps avoid unexpected changes to the execution plan that may occur when a hotfix or a security update is installed.

This is of course a good thing, but could also lead to that your application is not performing as well as it could. In addition, every fix would require you to turn it on manually, using a specific Trace Flag. While this might be good for us as SQL Server consultants, it might not be perfect for everyone. But there is in fact a different way if attacking this, by enabling Trace Flag 4199. This will effectively turn on all optimizer fixes in one switch, so you dont have to fiddle with every single one separately. You can enable trace flag 4199 at startup or in a user session. This trace flag has either global-level or session-level effect. To enable trace flag 4199, use the DBCC TRACEON command or use –T 4199 as a startup parameter. As always, you should NOT do this in production without verifying first in your test environment that the results are as expected.

And of course, we can help you with this and any other SQL Server project, just contact us and we will be glad to help!