Desktop refresh outperforms Report Server refresh

[MrNoob]

A little background on the issue that i am attempting to gather info for.  

My company is running out the On-prem report server, which is awesome, i am a huge fan of Power BI and have been pushing for it with upper management.  

Now we are running into an issue with the auto refresh feature on the report server.  95% of the time our refresh times out and fails on the server, however, runs fine on the desktop version.  Has anyone had this issue before? 

most of our queries are SQL based and you use that to pull the data we need for our reports.  Should we start using DAX language to query the server over SQL?

Looking for insight from outside sources, and anything will help on these performance issues

[Anonymous]

Since you mention that your queries are SQL I assume there's a database backend to this that is providng your dataset.

Have you tried running profiler on the data sources so you can see the SQL that is being issued when you do a refresh? If you take a look at this you can run the SQL on the DB and see what the query plans loook like. It's possible the SQL needs optimising or some indexes need adding to the underlying database.

Have you got any detail from the ReporTServer Db on the specific errors that are being produced by the refresh?

[MrNoob]

Yes, we have gotten some scheduled refreshes have completed, however other times they have not. 

Our IT and Data Base admins are in the process of seeing if the Database is indexed, and then we are in the process of optimizing our SQL code.  I will keep you posted but thank you for your insight, seems we may be on the right path.

[Anonymous]

This has been loosely addressed by Microsoft. Basically there's a process "msmdsrv.exe" that runs scheduled refreshes. It's actually a flavor of an SSAS instance. This can be a memory hog.

https://docs.microsoft.com/en-us/power-bi/report-server/scheduled-refresh

"The ability to load reports with imported data or DirectQuery, and the ability to perform scheduled refresh, rely on an Analysis Services instance being hosted alongside of the report server. As a result, this could result is unexpected memory pressure on the server. Plan your server deployment accordingly knowning that Analysis Services may be consuming memory alongside the report server."

The problem may not be on the SQL side. It could be on the PBIRS itself. You may need more memory installed. You need to stress-test your server and log the results. Watch the .msmdsrv.exe I mentioned earlier.

From what I know, a lot of the SQL calculations are done via tempdb which may run on the database/server you're pulling data from. If anybody could answer this question for me that would be appreciated as well. Let me know if you have any questions too.

[Anonymous]

It's probably also worth looking in the ReportServer DB to see what kind of errors are being reported in the dbo.SubscriptionHistory table.

On our system we see the following types of errors

The credentials provided for the SQL source are invalid.
Credentials are required to connect to the SQL source. 

Your Credentials are borken or not being passed correctly

Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.. The exception was raised by the IDbCommand interface.

Pretty self explanatory (your SQL needs optimising or your timeout needs to be longer

The current operation was cancelled because another operation in the transaction failed.

The database operation was cancelled because of an earlier failure.

These show up when several things are being refreshed and one fails,. The other requests get cancelled

XML for Analysis protocol error: The server response format is unexpected. All tags between an Exception element and the Messages section must be closed.

Lord alone knows what's going on here. But It seems to be related to older versions of SSAS when they are used as data sources

Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server). The exception was raised by the IDbCommand interface.

Someones broken your network or turned a server off (or you've typed the servername incorrectly

The command has been canceled.. The exception was raised by the IDbCommand interface.
The command has been canceled.. The exception was raised by the IDataReader interface.

These indicate you need to go look at the logs on the SSRS Server to see exactly what happened. these seem to be internal errors

You can use some code like this to get a list of the errors that have been produced. Somehting could of course be happening at a lower level as bhanson4 suggests like the in memory SSAS instance not having enough memory to do it's job properly. But I'd hope that a scan of the errors might point you at something meaningful or maybe suggest a pattern

SELECT SubscriptionHistoryID, 
		[Message], 
		JSON_VALUE(Details, '$.SessionID') AS SessionID,
		ErrData.ErrCode,
		ErrData.ErrMsg,
		JSON_QUERY(Details, '$.Errors')  AS Errs,
		Details
FROM	dbo.SubscriptionHistory  
	CROSS APPLY OPENJSON(JSON_QUERY(Details, '$.Errors')) WITH( ErrCode INT '$.ErrorCode', ErrMsg NVARCHAR(4000) '$.Message') AS ErrData
WHERE	Details <> ''