John Morehouse takes us through the step-by-step for enabling optimizer hotfixes in SQL Server:
There are a number of knobs and switches that are available to database administrators that can be used to enable better performance. There are three options in particular that this blog will be discussing, trace flag 4199, the database scoped configuration QUERY_OPIMIZER_HOTFIXES and the qeury hint ENABLE_QUERY_OPTIMIZER_HOTFIXES. Understanding how these options function will give you a hand up on ensuring the query optimizer is running as optimally as possible.
Let’s take a look at the three options.
Read on to learn more. There is some potential risk of regression with new optimizer updates, so standard rules around testing apply.