topic Re: PowerBI Gateway log sql in Report Server

PowerBI Gateway log sql

FrancescoRola — Fri, 20 Oct 2023 09:27:14 GMT

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

Re: PowerBI Gateway log sql

lbendlin — Sun, 22 Oct 2023 01:43:00 GMT

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

Re: PowerBI Gateway log sql

FrancescoRola — Wed, 25 Oct 2023 07:48:13 GMT

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??

Re: PowerBI Gateway log sql

lbendlin — Wed, 25 Oct 2023 11:25:04 GMT

For actual query impact you use the QueryExecutionReport logs.

Re: PowerBI Gateway log sql

FrancescoRola — Wed, 25 Oct 2023 13:44:16 GMT

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?

Re: PowerBI Gateway log sql

lbendlin — Wed, 25 Oct 2023 15:06:42 GMT

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.