Edit

Share via

Smoothing and throttling in Fabric Data Warehousing

  • Article

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

This article details the concepts of smoothing and throttling in workloads using Warehouse and SQL analytics endpoint in Microsoft Fabric.

This article is specific to data warehousing workloads in Microsoft Fabric. For all Fabric workloads and general information, see Throttling in Microsoft Fabric.

Compute capacity

Capacity forms the foundation in Microsoft Fabric and provides the computing power that drives all Fabric workloads. Based on the Capacity SKU purchased, you're entitled to a set of Capacity Units (CUs) that are shared across Fabric. You can review the CUs for each SKU at Capacity and SKUs.

Smoothing

Capacities have periods where they're under-utilized (idle) and over-utilized (peak). When a capacity is running multiple jobs, a sudden spike in compute demand might be generated that exceeds the limits of a purchased capacity. Warehouse and SQL analytics endpoint provide burstable capacity that allows workloads to use more resources to achieve better performance.

Smoothing offers relief for customers who create sudden spikes during their peak times while they have a lot of idle capacity that is unused. Smoothing simplifies capacity management by spreading the evaluation of compute to ensure that customer jobs run smoothly and efficiently.

Smoothing won't affect execution time. It helps streamline capacity management by allowing customers to size your capacity based on average, rather than peak usage.

  • For interactive jobs run by users: capacity consumption is typically smoothed over a minimum of 5 minutes, or longer, to reduce short-term temporal spikes.
  • For scheduled, or background jobs: capacity consumption is spread over 24 hours, eliminating the concern for job scheduling or contention.

Throttling behavior specific to the warehouse and SQL analytics endpoint

In general, similar to Power BI, operations are classified either as interactive or background.

Most operations in the Warehouse category are reported as background to take advantage of 24-hour smoothing of activity to allow for the most flexible usage patterns. With 24-hour smoothing, operations can run simultaneously without causing any spikes at any time during the day. Customers get the benefit of a consistently fast performance without having to worry about tiny spikes in their workload. Thus, classifying data warehousing as background reduces the frequency of peaks of CU utilization from triggering throttling too quickly.

Most Warehouse and SQL analytics endpoint operations only experience operation rejection after over-utilization averaged over a 24-hour period. For more information, see Future smoothed consumption.

Throttling considerations

  • Any inflight operations including long-running queries, stored procedures, batches won't get throttled mid-way. Throttling policies are applicable to the next operation after consumption is smoothed.
  • Warehouse operations are background except for scenarios that involves Modeling operations (such as creating a measure, adding or removing tables from a default semantic model, visualize results, etc.) or creating/updating Power BI semantic models (including a default semantic model) or reports. These operations continue to follow "Interactive Rejection" policy.
  • Just like most Warehouse operations, dynamic management views (DMVs) are also classified as background and covered by the "Background Rejection" policy. As a result, DMVs cannot be queried when capacity is throttled. Even though DMVs are not available, capacity admins can go to Microsoft Fabric Capacity Metrics app to understand the root cause.
  • When the "Background Rejection" policy is enabled, any activity on the SQL query editor, visual query editor, or modeling view, might see the error message: Unable to complete the action because your organization's Fabric compute capacity has exceeded its limits. Try again later.
  • When the "Background Rejection" policy is enabled, if you attempt to connect to a warehouse or run a new TSQL query in client applications like SQL Server Management Studio (SSMS) or Azure Data Studio via SQL connection string, you might see SQL error code 24801 and the error text Unable to complete the action because your organization's Fabric compute capacity has exceeded its limits. Try again later.

Best practices to recover from overload situations

Review actions you can take to recover from overload situations.

Monitor overload information with Fabric Capacity Metrics App

Capacity administrators can view overload information and drilldown further via Microsoft Fabric Capacity Metrics app.

For a walkthrough of the app, visit How to: Observe Synapse Data Warehouse utilization trends.

Use the Microsoft Fabric Capacity Metrics app to view a visual history of any overutilization of capacity, including carry forward, cumulative, and burndown of utilization. For more information, refer to Throttling in Microsoft Fabric and Overages in the Microsoft Fabric Capacity Metrics app.

An animated image showing the capabilities of the Fabric Capacity Metrics app.

Next step

How to: Observe Synapse Data Warehouse utilization trends

Related content