What are Optimizer Hints?
Normally, when you submit a query, the SQL Server will try to interpret the query and find the best possible execution plan for your query based on statistics, among other things. Normally this works very well. Occasionally, however, there are cases where the SQL Server miscalculates, either because of missing or outdated statistics, or because it misinterprets the query, or for some other reason makes an incorrect estimate of the query’s effort (if you want to know more about this, you should visit a PASS Essential by Grandmaster Ricken-San, for example.
How can you use Optimizer Hints?
Generally, suggestions or hints for the optimizer are given at the end of the query. The keyword
OPTION followed by brackets containing the different options for the query, followed by
USE HINT followed by brackets containing the optimizer hints separated by commas. If you would like to know which hints are possible on your system, the following query will help you:
SELECT [name] FROM sys.dm_exec_valid_use_hints
This system view, added in SQL Server 2017, lists all possible query hints available in your database. This is now a subset of the following attributes:
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS: if you change this hint, the way SQL Server estimates the number of rows in JOIN statements. Specifically, since version 2014, SQL Server does not necessarily assume that values occurring in a ‘JOIN’ predicate must actually be present in both tables; this assumption can be overridden with this hint.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES: Basically, SQL Server assumes that if there are several consecutive filters, they are independent of each other. This means that it assumes that a filter that filters 50% of the data on the entire table always does so, even if an equivalent filter has already been set previously. This can lead to situations where SQL Server massively underestimates the number of rows. This can be prevented with this note.
DISABLE_BATCH_MODE_ADAPTIVE_JOINS: Since SQL Server 2017 the SQL Server can delay the decision which join is the correct one for a query until after the first table scan. This note prevents this behavior.
DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK: In version 2017 the SQL Server has the possibility to remember the required memory for a query and if too much or too little memory has been reserved, to adjust it accordingly the next time the same query is executed. This can lead to unwanted side effects and therefore can be prevented with these hints.
DISABLE_DEFERRED_COMPILATION_TV: With this hint you control when the SQL Server Table Valued variables are resolved.
- DISABLE_INTERLEAVED_EXECUTION_TVF: This is about how SQL Server estimates the number of rows in a function that returns a table and is composed of multiple queries.
DISABLE_OPTIMIZED_NESTED_LOOP: This allows you to prevent a SORT operation from being used in the query plan to optimize a NESTED LOOP JOIN.
DISABLE_OPTIMIZER_ROWGOAL: For example, if your query contains a TOP, this modifies the number of rows expected, and the Query Optimizer usually adjusts accordingly. With this note, you can tell the Query Optimizer to ignore constructs that artificially reduce the number of rows returned (this includes, for example, an OPTION FAST 1000).
DISABLE_PARAMETER_SNIFFING: Normally, when making queries with parameters, SQL Server tries to use the first parameter passed to determine the query plan for all later parameters. This can be prevented with this option.
DISABLE_TSQL_SCALAR_UDF_INLINING: The point here is that in the 2019 and later versions, SQL Server will attempt to resolve functions and, if they become too expensive, add them directly to the query as scalar expressions or as sub-queries. This optimization can be switched off with this note.
DISALLOW_BATCH_MODE: This hint completely disables the Batch Mode, which SQL Server uses for Column Store Indexes, for the query.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS: Using this option allows SQL Server to quickly generate statistics for leading index columns in the query from the column’s historgram. Of course, this increases the compile time of the query, but may reduce the query time significantly.
ENABLE_QUERY_OPTIMIZER_HOTFIXES: Controls whether new changes in the Query Optimizer may be used in the query.
- FORCE_DEFAULT_CARDINALITY_ESTIMATION and FORCE_LEGACY_CARDINALITY_ESTIMATION: These two notes overwrite the respective behavior of the database, which method is used to estimate the number of rows in the tables.
QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n: This allows you to make the Query Optimizer behave as in a particular version of SQL Server.
QUERY_PLAN_PROFILE: This option controls how the query is seen in monitoring with Extended Events and the Profiler.
You can see that you have many ways to control the behavior of SQL Server for individual queries. Basically, however, just as in the Dojo the master rarely makes mistakes and you should assume that he is right, in the vast majority of cases you can assume that the Query Optimizer is right and selects the correct execution plan. You should therefore only in exceptional cases persuade him to do something else than he actually wants to do.