Edit

How to: Observe Fabric Data Warehouse utilization trends

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

Learn how to observe trends and spikes in your data warehousing workload in Microsoft Fabric using the Microsoft Fabric Capacity Metrics app.

The Microsoft Fabric Capacity Metrics app provides visibility into capacity usage for all Fabric workloads in one place. It's mostly used by capacity administrators to monitor the performance of workloads and their usage, compared to purchased capacity.

Prerequisites

Observe overall trend across all items in Fabric capacity

In the Fabric Capacity Metrics app, use the Multi metric ribbon chart to find peaks in CU utilization. Look for patterns in your Fabric usage that coincide with peak end-user activity, nightly processing, periodic reporting, etc. Determine what resources are consuming the most CUs at peak utilization and/or business hours.

This graph can provide high-level CU trends in the last 14 days to see which Fabric workload has used the most CU.

  1. Use the Item table to identify specific warehouses consuming most Compute. The Items table in the multi metric ribbon chart provides aggregated consumption at item level. In this view, for example, you can identify which items have consumed the most CUs.
  2. Select "Warehouse" in the Select item kind(s) dropdown list.
  3. Sort the Item table by CU(s), descending.
  4. You can now identify the items using the most capacity units, overall duration of activity, number of users, and more.

Drill through peak activity

Use the timepoint graph to identify a range of activity where CU utilization was at its peak. We can identify individual interactive and background activities consuming utilization.

The following animated image walks through several steps you can use to drill through utilization, throttling, and overage information. For more information, see Throttling in Microsoft Fabric.

An animated gif of the Metrics app showing the drill through steps.

  1. Select the Utilization tab in timepoint explore graph to identify the timepoint at which capacity utilization exceeded more than what was purchased. The yellow dotted line provides visibility into upper SKU limit. The upper SKU limit is based on the SKU purchased along with the enablement of autoscale, if the capacity has autoscale enabled.

  2. Select the Throttling tab and go to the Background rejection section, which is most applicable for Warehouse requests. In the previous sample animated image, observe that on October 16, 2023 at 12:57 PM, all background requests in the capacity were throttled. The 100% line represents the maximum limit based on the Fabric SKU purchased.

  3. Select the Overages tab. This graph gives an overview of the debt that is being collected and carry forwarded across time periods.

    • Add % (Green): When the capacity overloads and starts adding to debt bucket.
    • Burndown % (Blue): When the debt starts burning down and overall capacity utilization falls below 100%.
    • Cumulative % (Red): Represents the total overall debt at timepoints. This needs to be burned down eventually.

  4. In the Utilization, Throttling, or Overages tabs, select a specific timepoint to enable the Explore button for further drill through analysis.

  5. Select Explore. The new page provides tables to explore details of both interactive and background operations. The page shows some background operations that are not occurring at that time, due to the 24-hour smoothing logic. In the previous animated image, operations are displayed between October 15 12:57 PM to October 16 12:57 PM, because of the background operations still being smoothed at the selected timepoint.

  6. In the Background operations table, you can identify billing intervals with the highest CU consumption from user-initiated and system-initiated workloads.

    • The operations table displays completed billing intervals and their associated compute consumption.
    • To identify intervals with high CU consumption:
    1. Filter Item kind to Warehouse.
    2. Sort Total CU (s) in descending order.
    3. Record the Start and End times for the interval you want to investigate.

    Use the start and end times to correlate the billing interval with query activity in Query Insights and dynamic management views (DMVs).

    Note

    The Operation Id shown in the Capacity Metrics app no longer maps to the distributed statement ID for warehouse queries. To determine which queries contributed to compute consumption during a billing interval, query the queryinsights.exec_requests_history view using the interval's start and end timestamps.

    The following T-SQL example returns requests that were active during a specified billing interval:

    DECLARE @Start_Time DATETIME2(0) = '2026-08-04 8:00:00'
        ,@End_Time DATETIME2(0) = '2026-08-04 9:00:00'

SELECT [database_name],
       sql_pool_name,
       distributed_statement_id,
       login_name,
       allocated_cpu_time_ms / 1000.0 AS vcore_seconds
FROM queryinsights.exec_requests_history
WHERE start_time < @End_Time
AND end_time > @Start_Time;

    This query returns requests that overlapped with the specified billing interval. Use the results to identify workloads that contributed to the reported CU consumption.

  7. The Burndown table graph represents the different Fabric workloads that are running on this capacity and the % compute consumed by them at the selected timepoint.

    • The table entry for DMS is your Warehouse workload. In the previous sample animated image, DMS has added 26% to the overall carryforward debt.
    • The Cumulative % column provides a percentage of how much the capacity has overconsumed. This value should be below 100% to avoid throttling. For example, in the previous sample animated image, 2433.84% indicates that DMS used 24 times more capacity than what the current SKU (F2) allows.

Related content

  • Last updated on