High CPU Usage on Power BI RS (On premise)

[alanquadros]

Hello guys,

I have a question about Power BI Reporting Services (On Premise)

One of the users posted a report that, when this report starts to update the data, consumes 100% cpu and 90% memory.

Is there any way to know which report is this? So that I can guide you how to schedule the update

And how to limit the use of cpu and memory in the service? Or even by report?

regards,

[josef78]

There are many options how to monitor ...

-if you know which is problematic report, you are won, you can analyze separately in Power BI Desktop - Performance Analyzer and DAX Studio tool.

-if not, but these issues are only sometimes in specific time, you can try in ExecutionLog3 view. And/or in PBIRS\LogFiles folder to found problematic report

-but if is your server overloaded, and you are running hundreds of reports at same time, is not easy find it in logs. You need monitor load inExectuionLog3 view continuously, and need monitor CPU time and memory usage on OS on process level (e.g. if CPU load comes from msmdsrv (tabular storage engine), Microsoft.Mashup... (Refresh Data), or RSPowerBI (Power BI service) , ...). Depend on this you can continue, e.g. when problem is in refreshing data you can create scaleout PBI server dedicated for refreshing data, if problem in SSAS you can investigate more deeply using Dynamic Management Views, Profiler, and so on...

[v-deddai1-msft]

Hi @alanquadros ,

As mentioned by JeanMartinL, you can use ExecutionLog3 to view slow report . Here is a good document for your reference : https://insightsquest.com/2018/01/29/power-bi-report-server-monitoring/

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[rayng5000]

Great article!

Just FYI on ExecutionLog3 for the rest of us, it is a view and not a table; by defult it is setup not to capture data. You will need to work with your DBA to set up the following:

   1. Enable ExecutionLog3 view to begin capturing data. One of the view's porperty options allow you to do that.

   2. Set up an ETL process to capture the data into a table for access to historical data.

Best!

Ray

[alanquadros]

So... thanks for the help...
i'm starting to analyse... the Execution log... and i found some weird itens... with ItemPath = 'Unknown'

And i've tried to figure out ExecutionId and CatalogId without success...

does anyone know what is it ?! ^^

Regards,

[josef78]

'Unknown' value in ExecutionLog3 view (in case of PBI reports) mean that is report is not exist now. It mean this report was deleted or replaced in catalog.

[rayng5000]

Hopefully this blog post may help explain it:

What does value "unknown" mean in ItemPath field of executionlog3 view in Reportserver database in M...

[JeanMartinL]

Hello,

You could take a look at the view dbo.ExecutionLog3 in your ReportServer database, while not perfect (you won't find a CPU usage stats sadly) you might be able to find an outlier in TimeDataRetrieval, TimeProcessing or ByteCount.

Not a perfect solution but the best I can think of.

Regards,

[lbendlin]

start with the biggest impacts and work your way down. There's no magic to it, just hard work.

[alanquadros]

but which one to start ?! i dunno which one... 😞

there is a way to monitor resources by reports ?!

[lbendlin]

you're holding the problem wrong.

1. educate the developer about Power Query Diagnostics so they can improve the refresh performance

2. add more CPUs and more memory to your Report server machine. Much more.

[alanquadros]

But i have arround 200 reports... which one ?! 😞

[rayng5000]

@alanquadros:

From Power BI Desktop, naviagte to the 'View' tab, and check the 'Performance Analyzer' box. Then watch this video to understand how to use it and determine which of your 200 reports are the top resource hogs.

Introducing Performance Analyzer in Power BI Desktop - YouTube