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)
7 Comments. Leave new
It seems not to be supported at SQLServer 2012?
Thank you. We had similar issue which we faced in one of our recent migration from SQL 2008 R2 to 2016. Query runs much longer in 2016 compared to 2008 R2, we were thinking more on query optimizer as some of the code in 2008 were legacy codes. MS recommended us to change the compatibility level back to 100 or 110 as the query optimizer will try use the old one which would solve the cardinal estimate.
We had similar issue and we kept compatibility mode set to 130 but Set Legacy Cardinality Estimation ON.This resolved our issue.
Just be aware that running with that setting, we are doing that too in a number of places, stops new information from appearing in the execution plan xml that might help see why the plan is the way it is. I have seen this in SQL 2016 SP2 builds.
Dave can you help clarify “if in 130 it doesnt matter”?
a. I have SQL 2016 SP1 CU3 (201705 updates)
b. Compatibility mode for all dbs set to 130
c. Are the hotfixes for SQL 2016 included in SP1 and CU1-4 (soon to be installed CU4) automatically enabled by design for SQL 2016 when compatibility mode is 130?
Ah now it makes sense after reading:
https://blogs.msdn.microsoft.com/sqlserverstorageengine/2016/05/23/query-optimizer-additions-in-sql-server/
My understanding is that for any new optimizer fixes introduced after SQL 2016 RTM you either need trace flag 4199 on or the Query optimizer Fixes needs to be ON at the database level. We found this out for a fix in SQL 2016 SP1 CU1/2/3