Please enter your information to subscribe to the Microsoft Fabric Blog.

Thanks for subscribing to the Microsoft Fabric Blog. You’ll receive email notifications when we publish new posts.

Microsoft Fabric Updates Blog

  1. Updates
  2. All

Hints in Fabric Data Warehouse

What are hints?

Hints are optional keywords that you can add to your SQL statements to provide additional information or instructions to the query optimizer. Hints can help you improve the performance, scalability, or consistency of your queries by overriding the default behavior of the query optimizer. For example, you can use hints to specify the join type or the join order for the query.

How to use hints in Fabric SQL?

Fabric DW supports a variety of hints that you can use to optimize your queries. To use a query hint, you need to add the OPTION clause at the end of your query, followed by the name of the query hint and its optional parameters in parentheses. For example, the following query uses the HASH GROUP query hint to instruct the query optimizer to use a hash-based algorithm for the GROUP BY operation:

SELECT band_id, SUM(ticket_cost) FROM gigs GROUP BY(band_id) OPTION (HASH GROUP)

You can use multiple hints in the same query, separated by commas. For example, the following query uses the FORCE ORDER and MAX_GRANT_PERCENT query hints to instruct the query optimizer to preserve the join order specified in the query and to limit the memory grant to 20 percent of the available memory:

SELECT gigs.event_date FROM gigs INNER JOIN bands ON bands.id = gigs.band_id OPTION (FORCE ORDER, MAX_GRANT_PERCENT=20)

What hints are supported in Fabric SQL?

Join hints

  • MERGE JOIN, LOOP JOIN, HASH JOIN: Specifies the join algorithm that the query optimizer should use for the join operation. This can improve the performance of queries that involve large or complex joins.
  • REPLICATE: Causes a broadcast move operation, where a specific table to be replicated across all distribution nodes.
    • – Using `REPLICATE` with a `INNER` or `LEFT` join, the broadcast move operation will replicate the right side of the join to all nodes.
    • – Similarly, while using `REPLICATE` with a `RIGHT` join, the broadcast move operation will replicate the left side of the join to all nodes.
    • – When using `REPLICATE` with a `FULL` join, an estimated plan cannot be created.
  • REDISTRIBUTE [(colsCount)]: The REDISTRIBUTE hint ensures two data sources are distributed based on JOIN clause columns. It handles multiple join conditions, specified by the first n columns in both tables, where n is the column_count argument. Redistributing data optimizes query performance by evenly spreading data across nodes during intermediate steps of execution. The (columns_count) argument is only supported in Microsoft Fabric Warehouse.

Query hints

  • HASH GROUP: Specifies that the query optimizer should use a hash-based algorithm for the GROUP BY operation. This can improve the performance of queries that involve large or complex grouping sets.
  • ORDER GROUP: Specifies that the query optimizer should use a sort-based algorithm for the GROUP BY operation. This can improve the performance of queries that involve small or simple grouping sets.
  • MERGE UNION: Specifies that the query optimizer should use a merge-based algorithm for the UNION or UNION ALL operation. This can improve the performance of queries that involve sorted inputs.
  • HASH UNION: Specifies that the query optimizer should use a hash-based algorithm for the UNION or UNION ALL operation. This can improve the performance of queries that involve unsorted or large inputs.
  • CONCAT UNION: Specifies that the query optimizer should use a concatenation-based algorithm for the UNION or UNION ALL operation. This can improve the performance of queries that involve distinct or small inputs.
  • FORCE ORDER: Specifies that the query optimizer should preserve the join order specified in the query. This can improve the performance or consistency of queries that involve complex join conditions or hints.
  • FORCE SINGLE NODE PLAN/FORCE DISTRIBUTED PLAN: Allows user to choose whether to force a single node plan or a distributed plan for query’s execution.
  • 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. For example, OPTION(USE HINT(‘ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES’) ). In Fabric, the following hint names are allowed, which can change the behavior of CE derivation.
    • ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES
      • When calculating cardinality estimates for AND predicates for filters, Fabric will assume full correlation among filters. If you observe a high level of underestimation on AND predicates for filters, this hint can help produce a better estimate.
    • ASSUME_FULL_INDEPENDENCE_FOR_FILTER_ESTIMATES
      • When calculating cardinality estimates for AND predicates for filters, Fabric will assume full independence among filters. If you observe a high level of overestimation on AND predicates for filters, this hint can help produce a better estimate.
    • ASSUME_PARTIAL_CORRELATION_FOR_FILTER_ESTIMATES
      • When calculating cardinality estimates for AND predicates for filters, Fabric will assume partial correlation among filters. This is the default behavior in Fabric, so it is unlikely that this hint will help improve the estimates.
    • ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS
      • When calculating cardinality estimate for joins, this hint will use Simple Containment assumption instead of the default Base Containment assumption.

Conclusion

Hints are a tool that you can use to optimize your queries in Fabric DW. By using hints, you can provide additional information or instructions to the query optimizer and override its default behavior. However, hints should be used with caution and testing, as they can also have negative effects on the performance and scalability of your queries if used incorrectly or unnecessarily. Hints in code can become permanent and may not be updated by users as base table data changes. They can potentially prevent the system from using better plans for new data volumes or distributions. Therefore, you should always monitor and evaluate the impact of query hints on your queries and adjust them as needed.

Submit your feedback on Fabric Ideas and join the conversation on the Fabric Community. To get into the technical details, head over to the Fabric documentation. 

Related blog posts

Hints in Fabric Data Warehouse

Blog's featured image

Extracting deeper insights with Fabric Data Agents in Copilot in Power BI
May 19, 2025 by Amir Jafari

Co-author: Joanne Wong We’re excited to announce the upcoming integration of Fabric data agent with Copilot in Power BI, enhancing your ability to extract insights seamlessly. What’s new? A new chat with your data experience is launching soon in Power BI– a full-screen Copilot for users to ask natural language questions and receive accurate, relevant … Continue reading “Extracting deeper insights with Fabric Data Agents in Copilot in Power BI”

Read more
Blog's featured image

Warehouse Snapshots in Microsoft Fabric (Preview)
May 19, 2025 by Twinkle Cyril

Maintaining data consistency during ETL (Extract, Transform, Load) processes has long been a critical challenge for data engineers. Whether it’s a nightly pipeline overwriting key records or a mid-day transformation introducing schema drift, the risk of disrupting downstream analytics is both real and costly. In today’s fast-paced, data-driven world, even brief inconsistencies can break dashboards, … Continue reading “Warehouse Snapshots in Microsoft Fabric (Preview)”

Read more