topic Re: Analyse query performance in Data Engineering

Analyse query performance

DennesTorres — Sat, 02 Sep 2023 12:14:36 GMT

Hi,

I know the environment is a PaaS and most underlying details should not be a concern for us. However, there are high-level decisions which leads to consequences we may need to analyse. For example, how delta tables deal with updates and the decision to build dimensions with upsert or even delete.

I was analysing exactly this and it was difficult to identify the performance differences. It's difficult to identify when the query is reading the storage, reading a cache and the impact in the capacity.

I tried to identify this analysing the impact on the capacity using the app "Premium Capacity Utilization and Metrics". But this leads to a 2nd question: This App is mainly focused on dataset refreshes. On fabric, we have the SQL Endpoint and data warehouse which may involve queries directly to them, instead of dataset refreshes. I couldn't identify this information in the App.

I opened the App dataset in Excel and the Items table only contains dataset refreshes (or am I mistaken?). Is there another way to analyse the impact of a query in a SQL Endpoint to the capacity, for example? Identify if the query was using cache or not?

I know there are the API's, but I was trying to avoid build something "from the ground" querying the API's.

Kind Regards,

Dennes

Re: Analyse query performance

puneetvijwani — Sat, 02 Sep 2023 20:42:40 GMT

@DennesTorres you using this fabric capacity overview app ?? or premium capacity utilization ???

Re: Analyse query performance

DennesTorres — Sun, 03 Sep 2023 11:06:09 GMT

Hi,

Premium capacity Utilization. The portal took me to this one.

I will try to find this new one, if you have a link, it will help.

Kind Regards,

Dennes

Re: Analyse query performance

puneetvijwani — Mon, 04 Sep 2023 10:38:27 GMT

@DennesTorres If we Navigate to apps on left hand bar of Fabric portal

and get apps you can search for this one below and get it

Re: Analyse query performance

DennesTorres — Tue, 05 Sep 2023 01:14:24 GMT

Hi,

I tested the new App.

The link on the Admin portal still points to the old one, shouldn't it be fixed?

The report itself fails, I couldn't see it :

But when checking the dataset in the workspace I couldn't find any error message, so I tried to open it in Excel.

The Items table contains the missing Items, not only the Datasets anymore - that's exactly what I was missing.

However, I couldn't find yet the correct relations to list the operations in order of date. Is there a documentation of these tables, to identify these relations ?

Is there are documentation about the connection? Could we import this data into a lake/dw to work with this data in SQL? The dataset doesn't work as a source for the lake, it would need to be at least a dataflow. But if we could make the same connection to extract the usage information from the capacity, this would help. Is there a documentation about this?

Kind Regards,

Dennes

Re: Analyse query performance

puneetvijwani — Wed, 06 Sep 2023 20:16:54 GMT

@DennesTorres here are the relevant links
https://appsource.microsoft.com/en-us/product/power-bi/pbi_pcmm.microsoftpremiumfabricpreviewreport?exp=ubp8
Its seems driect query to underlying hosted dataset

Blog : https://blog.fabric.microsoft.com/en-us/blog/capacity-metrics-in-microsoft-fabric?ft=Core:category#:~:text=Capacity%20metrics%20in%20Fabric%20is,single%20set%20of%20turnkey%20analytics.