Microsoft Fabric Updates Blog

Data Warehouse Utilization Reporting in Fabric Capacity Metrics App

As announced today, we are excited to share that compute utilization reporting for Synapse Data Warehouse features: Warehouse and SQL Endpoint of the Lakehouse are now available in Public Preview for Microsoft Fabric in all regions!

In the capacity-based SaaS world of Microsoft Fabric, you can purchase a Fabric SKU which comes with a set of Capacity Units (CU). Back in June 2023 we announced the availability of Fabric SKUs. Workspaces are assigned to capacities and everything inside a workspace such as Warehouse, Lakehouse, Dataset, etc., consume capacity units based on consumption.

Similar to the rest of Fabric landscape, Warehouse compute also supports bursting capabilities for maximum performance. The bursting will be based on the SKU purchased (more details coming soon).

Warehouse and SQL Endpoint in Fabric Metrics app

The Microsoft Fabric Capacity Metrics app provides visibility into capacity usage for all Fabric workspaces tied to a capacity. It is used by capacity administrators to monitor the performance of workloads and their usage, compared to purchased capacity. Consider the following use cases:

  • Identify CU usage trends across warehouse items within a capacity.
  • Monitor overload information, and drill through specific timepoints to identify users/operations causing spikes.

Warehouse operation categories

Both Data Warehouse and SQL endpoint items roll up under “Warehouse” in Metrics app, as they both use SQL compute.

A screenshot of a computer

Description automatically generated

The operation categories seen in this view are:

A screenshot of a graph

Description automatically generated

1. Warehouse Query: Compute charge for all user generated and system generated T-SQL statements within a warehouse.

2. SQL Endpoint Query: Compute charge for all user generated and system generated T-SQL statements within a SQL Endpoint.

3. Onelake Compute: Compute charge for all reads and writes for data stored in OneLake.

Timepoint – Explore graph

A screenshot of a computer

Description automatically generated

This graph in the Microsoft Fabric Capacity Metrics app shows utilization of resources compared to capacity purchased. 100% of utilization represents the full throughput of a capacity SKU and is shared by all Fabric experiences. This is represented by the yellow dotted line. The Logarithmic scale option enables the Explore button, which opens a detailed drill through page.

In general, operations are classified either as interactive or background, and denoted by color. All operations in Warehouse category are reported as background to take advantage of 24 hour smoothing of activity to allow for the most flexible usage patterns.

Timepoint – Drill through graph

This table in the Microsoft Fabric Capacity Metrics app provides a detailed view of utilization at specific timepoints. The amount of capacity provided by the given SKU per 30-second period is shown, along with the breakdown of interactive and background operations.

Top use cases for this view include:

  • Identification of a user who scheduled or ran an operation: values can be either “User@domain.com”, “System”, or “Power BI Service”.
    • Examples of user generated statements include running T-SQL queries or activity in the Fabric portal, such as the SQL Query editor or Visual Query editor.
    • Examples of “System” generated statements include metadata synchronous activities and other system background tasks that are run to enable faster query execution.
  • Identification of an operation status: values can be either “Success”, “InProgress”, “Cancelled”, “Failure”, “Invalid”, or “Rejected”.
    • The “Cancelled” status are queries cancelled before completing.
    • The “Rejected” status can occur because of resource limitations.
  • Identification of an operation that consumed many resources: sort the table by Total CU(s) descending to find the most expensive queries, then use Operation Id to uniquely identify an operation. This is the distributed statement ID, which can be used in other monitoring tools like dynamic management views (DMVs) for end-to-end traceability, such as in dist_statement_id in sys.dm_exec_requests.

For more information on Fabric warehouse usage reporting, please check out our documentation Billing and utilization reporting.

Related blog posts

Data Warehouse Utilization Reporting in Fabric Capacity Metrics App

February 22, 2024 by Miquella de Boer

We are very excited to announce a lot of new improvements to data destinations in dataflows gen 2. Here is an overview of the new improvements and how to get started. After you have cleaned and prepared your data with dataflows gen 2, you want to land your data into a destination. This is possible … Continue reading “Dataflows gen 2 data destinations and managed settings”

February 12, 2024 by Naama Tsafrir

Today, organizations are experiencing massive growth in the volume of data they handle. As a result, they face an ever increasing need to be able to organize and manage that data in a logical way that facilitates targeted usage and efficient governance. To meet this challenge, organizations are shifting away from traditional IT-centric data architectures, … Continue reading “Easily implement data mesh architecture with domains in Fabric”