How to identify specific operations from Fabric Capacity Metrics

[joakimfenno]

Today our capacity exceeds the limit
there are two background operations of type Warehouse Query and user Power BI Service that consumes a lot of CU which we have not seen before

How can we understand what these operations are? I tried the explore data (preview) function to drilldown but I dont understand/are able to identify more information about the operations

[frithjof_v · Accepted solution]

Do you find the Operation Id in the capacity metrics app?

If yes, it's possible to use this Operation Id to make a lookup to the dist_statement_id column (perhaps it's called distributed_statement_id now, ref. the second link below) in Data Warehouse system views.

See the end of this blog:

https://blog.fabric.microsoft.com/en-us/blog/data-warehouse-utilization-reporting-in-fabric-capacity...

This blog is very relevant:

https://bradleyschacht.com/using-sempy-to-get-sql-query-cu-cost-from-the-fabric-capacity-metrics-app

Other related options:

- DAX studio

- Power BI Desktop Performance Analyzer 

- Azure Log Analytics (requires paid license, I think)

[joakimfenno]

Thanks, I can map the transaction

[v-shex-msft]

HI @joakimfenno,

>>And if this is the related query I dont understand why the user is Power BI Service

The user means who send the request, it means these request send from power bi service related api. (e.g. backend operations, authorizations)

BTW, if you want to know more about capacity metrics compute and usages, you can refer to the following link:

Understand the metrics app compute page - Microsoft Fabric | Microsoft Learn

Regards,

Xiaoxin Sheng

[Expiscornovus]

Hi @joakimfenno,

Microsoft has a article with a bit more details about these interactive and background type of operations:

https://learn.microsoft.com/en-us/fabric/enterprise/fabric-operations

You could also look at some other monitoring options which could give you a bit more details, like for example query activities. Maybe you have some long running queries which you could inspect?

https://learn.microsoft.com/en-us/fabric/data-warehouse/monitoring-overview

https://learn.microsoft.com/en-us/fabric/data-warehouse/query-activity

[joakimfenno]

thanks @Expiscornovus 

I might have find the issue in terms of a long running query in my Warehouse. Not sure if I can track it back to the operation in the Fabric Capacity Metrics report though. And if this is the related query I dont understand why the user is Power BI Service