cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FilipK
Helper II
Helper II

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:

FilipK_1-1618501700820.png

 

The query itself is quite easy. It starts with Select ... and ends like this:

FilipK_2-1618501802391.png

 

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?

 

 

1 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

View solution in original post

6 REPLIES 6
GilbertQ
Super User II
Super User II

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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:

FilipK_0-1618552310305.png

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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

It doesn't help. 

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

 

FilipK_0-1618811653933.png

 

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.

FilipK_1-1618811770028.png

 

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

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:

 

FilipK_0-1620366111182.png

 

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?

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

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!