Disclaimer: This is work in progress intended to consolidate information from various sources for learning purposes. For the latest information please consult the documentation (see the links below)!
Last updated: 6-Apr-2024
[Microsoft Fabric] Hints in Warehouse- {def} keywords that users can add to SQL statements to provide additional information or instructions to the query optimizer [2]
- options or strategies specified for enforcement by the SQL Server query processor [1]
- applicable to SELECT, INSERT, UPDATE, or DELETE statements [1]
- {benefit} help improve the performance, scalability, or consistency of queries by overriding the default behavior of the query optimizer [2]
- {type} join hints
- specify what join strategy/algorithm used between two tables [3]
- improves the performance of queries that involve large or complex joins [2]
- specified in the FROM clause of a query [1]
- if a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query [3]
- based on the position of the ON keywords [3]
- when a CROSS JOIN is used without the ON clause, parentheses can be used to indicate the join order [3]
- looping
- via LOOP JOIN
- {restriction} can't be specified together with RIGHT or FULL as a join type [3]
- hashing
- via HASH JOIN
- merging
- via MERGE JOIN
- REPLICATE
- causes a broadcast move operation
- a specific table to be replicated across all distribution nodes [2]
- with INNER or LEFT` join
- the broadcast move operation will replicate the right side of the join to all nodes [2]
- with RIGHT join
- the broadcast move operation will replicate the left side of the join to all nodes [2]
- with FULL` join
- an estimated plan cannot be created [2]
- REDISTRIBUTE [(colsCount)]
- ensures two data sources are distributed based on JOIN clause columns [2]
- handles multiple join conditions, specified by the first n columns in both tables, where n is the column_count argument [2]
- redistributing data optimizes query performance by evenly spreading data across nodes during intermediate steps of execution [2]
- the (columns_count) argument is only supported in MF warehouse [2]
- {type} query hint
- specify that the indicated hints are used in the scope of a query [3]
- affect all operators in the statement
- [UNION only the last query involving a UNION operation can have the OPTION clause [3]
- specified as part of the OPTION clause [3]
- Error 8622 occurs if one or more query hints cause the Query Optimizer not to generate a valid plan [3]
- used via the OPTION clause at the end of a query [2]
- followed by the name of the query hint and its optional parameters in parentheses [2]
- multiple hints can be used in the same query, separated by commas
- e.g. FORCE ORDER and MAX_GRANT_PERCENT
- instruct the QO to preserve the join order specified in the query and to limit the memory grant to 20 percent of the available memory
- {hint} HASH GROUP
- specifies that the QO should use a hash-based algorithm for the GROUP BY operation [2]
- {benefit} can improve the performance of queries that involve large or complex grouping sets [2]
- {hint} ORDER GROUP
- specifies that the QO should use a sort-based algorithm for the GROUP BY operation [2]
- {benefit} can improve the performance of queries that involve small or simple grouping sets [2]
- {hint} MERGE UNION
- specifies that the QO should use a merge-based algorithm for the UNION or UNION ALL operation [2]
- {benefit} can improve the performance of queries that involve sorted inputs [2]
- {hint} HASH UNION
- specifies that the query optimizer should use a hash-based algorithm for the UNION or UNION ALL operation [2]
- {benefit} can improve the performance of queries that involve unsorted or large inputs [2]
- {hint} CONCAT UNION
- specifies that the QO should use a concatenation-based algorithm for the UNION or UNION ALL operation [2]
- {benefit} can improves the performance of queries that involve distinct or small inputs[2]
- {hint} FORCE ORDER
- specifies that the QO should preserve the join order specified in the query [2]
- {benefit} can improves the performance or consistency of queries that involve complex join conditions or hints [2]
- {hint} FORCE SINGLE NODE PLAN/FORCE DISTRIBUTED PLAN
- allows to choose whether to force a single node plan or a distributed plan for query’s execution [2]
- {hint} USE HINT
- adds one or more extra hints to the query processor, where the hints are specified with a hint name inside single quotation marks inside OPTION clause [2] OPTION(USE HINT(‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’))
- used with several hint names, changing the behavior of CE derivation
- {hint name} ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
- applies when calculating cardinality estimates for AND predicates for filters [2]
- MF assumes full correlation among filters when a high level of underestimation on AND predicates for filters is observed [2]
- [SQL Server] equivalent TF 4137 [4]
- {hint name} ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES
- applies when calculating cardinality estimates for AND predicates for filters [2]
- MF assumes full independence among filters [2]
- if a high level of overestimation on AND predicates for filters is observed, this hint can help produce a better estimate [2]
- {hint name} ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
- applies when calculating cardinality estimates for AND predicates for filters [2]
- {default} MF assumes partial correlation among filters [2]
- ⇐ it is unlikely that this hint will help improve the estimates [2]
- {hint name} ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
- applies when calculating cardinality estimate for joins [2]
- uses Simple Containment assumption instead of the default Base Containment assumption [2]
- [SQL Server] equivalent TF 9476 [4]
- {type} table hints
- none enforced currently
[1] Microsoft Learn (2025) SQL: Hints (T-SQL) [link]
[3] Microsoft Learn (2025) SQL: OPTION clause (T-SQL) [link]
[4] Microsoft Support (2016) KB3189813 - Update introduces USE HINT query hint argument in SQL Server 2016 [link]
[R1] Microsoft Learn (2025) SQL Server: Query hints (T-SQL) [link]
[R2] Most Useful Query Hints [link]
MF - Microsoft Fabric
TF - Trace Flag
QO - Query Optimizer