Understanding how to enable SQL Server Optimizer Hotfixes

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.

Trace Flag 4199

In dealing with any trace flag, it is vital that you understand as to what the trace flag will enable or disable and how it pertains to your individual workloads.  Some trace flags are benign, such as TF3226 which removes successful backup messages from the log, others can be quite intrusive and cause havoc.   Make sure you review any documentation on the respective trace flag prior to implementation.

The official verbiage from Microsoft on trace flag 4199 is:

“Enables Query Optimizer (QO) fixes released in SQL Server Cumulative Updates and Service Packs.

QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 being enabled.” 

What this really implies is that if your database is at the most current compatibility level for your version of SQL server, then the query optimizer will utilize all of the QO hotfixes up to the release to market (RTM) version but nothing beyond the RTM version.

For example, if you are running SQL Server 2017 CU20 the database is set at the appropriate compatibility level (140), and TF4199 is disabled, the query optimizer will only utilize any hotfixes for it up to 2017 RTM.   Any query optimizer hotfix that was delivered in subsequent cumulative updates will not be utilized.

If you enable trace flag 4199, this instructs SQL Server to implement any query optimizer hotfix that was delivered after the RTM version.   Note that enabling this trace flag will be applied across all databases.  It’s an all or nothing trace flag as most trace flags are.

The table below (from Microsoft documentation explains the matrix of outcomes when dealing with trace flag enabled or disabled.

If you are running in Azure SQL Database, you don’t have the ability to enable this particular trace flag, which is where the database scoped configuration becomes useful. If you are running on Azure SQL Managed Instances, you can enable the trace flag just like you would if on-premises.

Enabling this trace flag would also require a restart of the SQL Server services if you want to persist it upon a restart. You can enable this trace flag by setting up a startup parameter in the SQL Server Configuration Manager.  You can check for the existence of trace flags by using DBCC TRACESTATUS().

QUERY_OPIMIZER_HOTFIXES

Enabling trace flag 4199 is a global trace flag for the entire instance.  This means that it’ll apply the configuration across all databases.  What if you have a mixture of databases that may or may not be able to take advantage of any hotfixes?  Enabling the trace flag could cause issues.  This is where the QUERY_OPIMIZER_HOTFIXES database scoped configuration comes in to play.

This configuration allows you to enabled query optimizer hotfixes post the release to market version for any specific database. This also allows you to enable it for Azure SQL Database since we don’t have the ability to enable the trace flag within that product.

ALTER DATABASE SCOPED CONFIGURATION QUERY_OPTIMIZER_HOTFIXES = ON;

The query above has to be executed within the context of the individual database.  You can also enable this configuration in Azure SQL Managed Instances databases.

ENABLE_QUERY_OPTIMIZER_HOTFIXES

If you didn’t want to enable these hotfixes at the instance level or the database level, there’s a third option.  You can also enable it as a query hint.  Keep in mind that the query optimizer will want to pick the best execution plan possible.  Use query hints as sparingly as possible and make sure to ask yourself if you are really smarter than the optimizer.  If the answer is no, you aren’t smarter than the optimizer then don’t use a query hint.  So, it goes without saying that my recommendation is to not use query hints unless that’s the only way to solve an issue.

SELECT * FROM Person.Address 
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION ( USE HINT ('ENABLE_QUERY_OPITMIZER_HOTFIXES'));
GO

Summary

In this post I’ve shown three methods on how to implement the most recent query optimizer hot fixes.  Before putting any of them into production, make sure that you verify your respective workloads again them.  Make sure to also review all of the cumulative updates or service packs since initial release to see what query optimizer issues were fixed.  You might be missing out of a fix that will help solve a performance problem.

© 2020, John Morehouse. All rights reserved.