Forum Discussion
Analyse query performance
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
5 Replies
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.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
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 itHi,
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
