Forum Discussion
Desktop refresh outperforms Report Server refresh
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?
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.
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.
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 <> ''
