Reports sometimes taking 60+ seconds to load (SSAS live connection)

[Kashinoda]

Hi,

We have an issue on our server where reports are sometimes taking a long time to load. This usually happens when the server has been idle for a period but it may happen when a new user loads reports for the first time (it's hard to test due to sporadic nature). This happens on datasets that are regularly processed as well as those that aren’t. The DAX queries are simple and it's a refined model we've used for years, no issues in Power BI Desktop.

In SQL Profiler I can see the following events and timings when the report is loading.

Discover End -  56 Seconds
Audit Logout - 56 Seconds

Query End - 52 Seconds

Query End - 52 Seconds

Query End - 52 Seconds

Query End - 52 Seconds

In IE and Chrome using development console I can see the above queries, either with a 'Stalled' or 'Waiting' timing message.

Prior to this there are a series of 401 errors, this may be a red herring as these are only visible in IE. The issue is present regardless of browser.

I've disabled Loopback Check on the server to remedy the potential authentication issues above. I've also setup a job which runs every two minutes and sends queries to the Tabular Model - unfortunately this doesn't seem to keep the data 'warm'. There are no notable entries in PBIRS logs either.

The server is a Zeon E5-2560 4 Core with 48GB of RAM and Enterprise SQL, error present on older and latest version of PBIRS. Using Perfmon I can see we're no where near any memory limitation in SSAS and should be caching everything.

I'm not really sure what else to try, any feedback greatly appreciated.

[Kashinoda]

Bump 🙂