During recent SQL Server Performance Tuning Practical Workshop I receive following question about Query Optimizer Fixes.
Question: What actually are Query Optimizer Fixes under Database Scoped Configurations? Should we turn Query Optimizer Fixes?
Answer: Before I answer this question, please note that this feature is introduced in SQL Server 2016. So if you are using any other version of SQL Server, you may not see this feature in your SQL Server Management Studio.
Now let us first understand what this particular setting is all about.
Before SQL Server 2016, whenever any hotfix or cumulative updates were released, query optimizer improvements were not automatically effective in SQL Server. The reason for this was very simple – if the new query optimizer changes were effective, there was risk of change in behavior for queries. Though we expect that this setting will improve performance, there was a good chance that some queries may perform very bad. It was always suggested that we do hotfix or cumulative updates on the development server first and once we are satisfied with the changes, should deploy it in production.
Once we deploy hotfix to the production if we want to get advantage of the new changes in the query optimizer we had to turn on trace flag 4199. If we do not enable trace flag 4199 even though we have a new patch on production server, our query optimizer will not use the new improved logic.
During my Comprehensive Database Performance Health Check, we often talk about this feature in detail.
However, beginning with SQL Server 2016, all the query optimizer improves are now based on compatibility level. This is one of the reasons, I recommended higher compatibility level for latest version of SQL Server. As soon as you change your database compatibility level to SQL Server 2016 (130), all the previous optimization will be effective, regardless of the trace flag 4199 turned on or off.
So here is the final question –
Should we leave Query Optimizer Fixes ON or OFF?
I know if I say either of the answer, there will be some individual who would be not be happy. However, let me express my point of view.
If you are using SQL Server 2016 and your compatibility level is set to SQL Server 2016 (130), you can ignore this safely. You can set it on or off, it does not matter.
If you are using SQL Server 2016 and your compatibility level is set to an earlier version of SQL Server (110,120), you can consider keeping it on after proper testing your system.
The best scenario is that you use SQL Server 2016 and keep your compatibility level to 130 – the best of the both the worlds.
Reference: Pinal Dave (https://blog.sqlauthority.com)