🏭🗒️Microsoft Fabric: Query Optimizer in Warehouse [Notes]

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-2025

[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

References:
[1] Microsoft Learn (2025) SQL: Hints (T-SQL) [link]

[2] Microsoft Fabric Updates Blog (2025) Hints in Fabric Data Warehouse [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]

Resources:
[R1] Microsoft Learn (2025) SQL Server: Query hints (T-SQL) [link]
[R2] Most Useful Query Hints [link]

Acronyms:
MF - Microsoft Fabric
TF - Trace Flag
QO - Query Optimizer