Forum Discussion
Multiple refreshes at the same time as root cause for timeout?
I experience a timeout problem when running dataset refreshs during midnight.
DataSource.Error: <pii>Microsoft SQL: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</pii>. DataSourceKind = <pii>SQL</pii>. DataSourcePath = <pii>.....</pii>. Message = <pii>Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.</pii>. ErrorCode = <pii>-2146232060</pii>. Number = <pii>-2</pii>. Class = <pii>11</pii>. ;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.
We see that some querries are performed simultaneously (three times between 12AM and 1 AM) on the DB that causes probably a timeout:
The query itself is quite easy. It starts with Select ... and ends like this:
The report itself contains 4 other Queries that are native, too which behaves identically (also querried 3-4 times simultaneously).
It's native query and incremental refresh is activated. Are there any ideas what can cause such error?
After playing a whil I think the behaviour is now understood.
When pushing a report to the PBI workspace the first thing what is done there is to update the whole dataset. What I've experienced is that the dataset is not updated at once. Instead it is splitted (believe it has to do with incremental refresh) into different time ranges. That's the reason for the querries with different rime ranges. And for all my refreshes fails after uploading it was tried again and again.
The 2nd phenoma was multiple execution of the query. That was due to the small query duration threshold of 10 minutes.
Solution:
- Increase the query timeout time.
- Scale up SQL database before running a complete refresh (after uploading the report) and scale down afterwards when it's only about refresh of the last day
6 Replies
Hi FilipK
I would chat to your DBA, it could be that say a DB backup or maintenance is happening at the same time you are trying to run the queries.
What happens if you run the refresh at another time, do you run into the same issues?
If not it could be something on the SQL server causing the issue.
