SQL Server Updates Tricked Me for Years

Monica Rathbun, 2018-02-21

When applying a new SQL Server cumulative update, hot fix, or upgrade SQL Server doesn’t always apply all the fixes in the patch. When you upgrade the database engine in-place, databases you had already 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 negative impact to 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 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 in effect.

Things changed starting with SQL Server 2016. If you look under 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 safe guard 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 start up. 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.

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads