Multiple refreshes at the same time as root cause for timeout?

[FilipK]

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?

[GilbertQ]

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.

[FilipK]

I'll can try. The odd thing is that at midnight and 5AM the queries are executed mulitple times.

Attached the backup times of the Azure Resource:

As you can see the backup is done at different times than.

[GilbertQ]

What happens if you change the time to say 1AM?

[FilipK]

It doesn't help. 

When changing to another time the query is still executed more than once.

What helps is if I increase the database performance (e.g. from 100DTU to 400DTU). The result is as follows. Doing this the execution is only performed once per timeframe.

Is there a retry triggered by PBI if there is no answer from the data source in a certain time?

[FilipK]

Dear all,

since last time when I reported that issue it became worse and I can provide some further details with the hope by this you can help me.

We experienced dozen of failed refrehes the lost couple of days and even worse no succesful. Actually the refresh from local PC end up in timeout as reported earlier.

So it was time to react. 

I change the timeout option for all of the 4 tables that are consumed like this:

 Source = Sql.Database(Databaseserver, Database, [CommandTimeout=#duration(0, 0, 60, 0)]),
    dbo_View_WC = Source{[Schema="dbo",Item="View_WC"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_View_WC , each [msgtimelocal] > RangeStart and [msgtimelocal] <= RangeEnd),

RangeStart: 01/01/2021

RangeEnde: 05/06/2021

It succeeded after 40 minutes (be aware the dataset is not big. The pbix after refresh is 15 mb big)

After uploading to PBI workspace the refresh run for 5 hours and failed (timeout).

In SQL Azure query insights I identified some real odd behaviour, I can't explain myself but perhaps lead to the solution:

You can see that refresh from desktop of the biggest table (grey) lasted around 25 minutes.

After successfully uploading the report + dataset the triggered refresh from workspace took already 31 minutes (yellow).

Crazy thing: the rangeStart and rangeEnd was set to an unexpected range:

where [_].[msgtimelocal] > convert(datetime2, '2020-01-01 00:00:00') and [_].[msgtimelocal] <= convert(datetime2, '2021-01-01 00:00:00')

Then the next query (light blue) tried to read out the same table but with other time ranges:

where [_].[msgtimelocal] > convert(datetime2, '2021-04-01 00:00:00') and [_].[msgtimelocal] <= convert(datetime2, '2021-05-01 00:00:00')

And this happend also for the green block again.

Where does that come from? Can anybody help with that new information?

[FilipK · Accepted solution]

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:

1. Increase the query timeout time.
2. 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