Slow On-Premise Report Server Performance /powerbi/api/explore/reports/.../querydata

[brethomp]

I am experiencing some slowness on only the Web portal of our Power BI Reports Server (on-premise) install. May 2022 version deployed. The desktop client is not affected. It is only the web portal that is slow. I am testing with a tinny report. One tab, 2 visuals, one data table with fewer than 100 records and around 20 columns. Should be fast and is fast in the desktop. All reports on the server are similar, but I will focus on this small report to isolate the issue.

The report loads slowly, usually 40 seconds before you can interact with it. But it might be better to focus on the slow interactions. If I click a line item in one visual, it takes an average of 5 seconds to filter the other visual. Using the network monitor I can see that it is a call to https://[FQDN]/powerbi/api/explore/reports/[Report GUI]/querydata that is taking all of time.

I have seen similar posts to the "me" endpoint taking a lot of time but that is not our issue.

Running a browser on the server where PBI SSRS service is installed, using a “localhost” URL, has the same issue, so I don’t think this is a network issue between the browser and PBI server.

I don't believe at this point it should be contacting the SQL server, but someone correct me if that is in error.

Could anyone point me in a direction to start looking into this?

[brethomp · Accepted solution]

The RSPortal log file was the key for our issue. There was a Windows Auth error in there that was taking up the majority of the time. We are using a Domain account for the PowerBI serveice to run under and that account did not have the privliges needed.

[Anonymous]

HI @brethomp 

Could you tell more about which Domain Account and where to configure it in PBIRS?

I'm facing similar issue (I guess).

Thank you.

[d_gosbell]

This does not sound like normal behaviour for a quiet server. You could try checking the VM Host, if that is overly busy you might not see that on the Report Server VM. Or you could try checking some of the Report Server log files. Particularly the RSPortal and RSPowerBI ones to see if there are any errors or retry messages there.

[d_gosbell]

This sounds a bit like a resource issue if the same report is fast on desktop.

I'd start by checking the CPU and RAM usage of the server in performance monitor. And if your server is on a VM check with your VM admins that it it not using memory ballooning (or similar). VM administrators love using stuff like this as it lets them fit more servers on a host, but it kills the performance of something like Power BI which needs to access and allocate memory quickly. If your server is memory constrained it might have to evict other models from memory before loading the one for the report. If you have a VM with memory ballooning you might not see any issues on the VM, but you see strange pauses like this where the VM is requesting access to memory from the hypervisor. If the CPUs are constantly on a high percentage of usage you might need more cores or faster hardware. 

[brethomp]

Thank you d_gosbell, I should have mentioned resources in my initial post. It is definitely not that. The test report is small and if I watch the server, there is hardly even a small blip in the resources when this report is run. And the server is not be used for other task or busy at all. I will look into the VM memory settings but the test report is so small I can't imagine it is requiring a big chunk of memory to be created.

Any other suggestions?