When applying a new SQL Server cumulative update, hot fix, or upgrade to SQL Server, it doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you already had stay at their pre-upgrade compatibility level, which means they run under the older set of optimizer rules. Additionally, many optimizer fixes are not turned on. The reason for this is that while they may improve overall query performance, they may have a negative impact on some queries. Microsoft actively avoids making breaking changes to its software.
To avoid any negative performance impacts, Microsoft has hidden optimizer fixes behind a trace flag, giving admins the option to enable or disable the updated fixes. To take advantage of optimizer fixes or improvements you would have to enable trace flag 4199 after applying each hot fix or update or set it up as a startup parameter. Did you know this? This was something I learned while working with an existing system, years into my career. I honestly assumed it would just apply any applicable changes that were in the patch to my system. Trace flag 4199 was introduced in the SQL Server 2005-era. In SQL Server 2014, when Microsoft made changes to the cardinality estimator they protected the changes with trace flags as well, giving you the option to run under compatibility level 120 and not have the cardinality estimator changes take effect.
Things changed starting with SQL Server 2016. If you look at database properties, you will notice an option called Query Optimizer Fixes which is defaulted to OFF. Unlike trace flags, I think this option brings this to the forefront, as many of us like to dig through properties and configurations which lead us to find out information like this. This defaulted to OFF still provides systems with the same safeguard as trace flag 4199. Now it can be controlled and enabled on a database level for all hot fixes, cumulative updates, and upgrades.
Query Optimizer Fixes must be set to ON for to be enabled automatically for compatibility levels less than 130 (SQL Server 2016). As you move from level to level you can enable all optimizer changes from previous updates just by changing to a higher compatibility level. There is no longer a need to use the trace flag to take advantage.
So, what’s the point of enabling this option if I can take advantage of optimizer changes just by changing compatibility levels? Well, the flag still applies for any future updates to SQL Server or potentially you are stuck at a lower compatibility level due to a third-party application or code that cannot be upgraded, but you still want to take advantage of optimizer fixes post updates. If you enable this option, you will no longer have to remember to enable trace flag 4199 post install and or have it enabled at startup. Now, of course, if you have this option set in production I hope you have already tested fixes in development and are free to have these optimizer changes implemented.
You can read more about the history of trace flag 4199 here.