PowerBI Gateway log sql

[FrancescoRola]

I'm having problems with the Powerbi and sql management studio because I can't locate reports or datasets that weigh down my sql server with report queries that are no longer under my control I would like to sparer if there is a way to locate these queries using the dilog files of the powerbi gateway by cross-referencing with the sql activity monitor data

[lbendlin · Accepted solution]

Yes, you can consult the QueryStartReport log files on the gateway cluster members. They include the connection details, the query text AND the culprit (the dataset that initiated the query.

The query text is Base64 encoded.

The dataset is in the EvaluationContext column

[FrancescoRola]

Yes yes I know, the problem is that in this way I don't get a way to identify the "heavy" queries that I have identified from the activity monitor of the sql server because in the powerbi logs (I think) the query that populates the single visual is shown or at least they have a different synassse and it is difficult for me to create a script to couple the queries from the logs and the queries from the activity monitor do you have any advice to give me??

[lbendlin]

For actual query impact you use the QueryExecutionReport logs.

[FrancescoRola]

No , my goal is to identify the queries that are weighing down my SQL server and they are queries "launched" by the user of the SQL gateway and by comparing the queries from the PowerBI logs and from the SQL Server Activity Monitor I can't identify them Do you have any solution to recommend?

[lbendlin]

Your main goal has to be to reduce the runtime of queries.  Query duration is by far the most important KPI in Power BI.  The QueryExecutionReport help provide data for that, including for all queries against your SQL server. You can also use the Fabric Capacity Metrics app to identify the long running queries and resource hoggers.