Power BI Desktop Timeout expired.

[hassanwasef]

Hello,
I have an issue while trying to refresh data from datasource in power bi desktop, the error

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.. The exception was raised by the IDbConnection interface."
is shown and no visuals get drawn. 

I use an Azure SQL as a data source with the S1 Standard plan of 20 DTU. 
I am not sure if i can increase the max pool size of azure sql, , or i should upgrade to a premuim plan. and how to calculate the proper plan to fit the transactions performed by power bi.

the DTU consumtion of Azure SQL becomes 100% when i open or work on the Power BI visuals. 

I appreciate any recommendations or suggestions.
Thanks 

[hassanwasef]

@v-shex-msft@alaeddin14  Hello, The problem is solved right after upgrading the Azure SQL plan to Premuim1. 
the confusing thing is that the error message is not relevant.
I don't know what is the relation between number of connection pools, PowerBI and Azure SQL Plans. any clues? 

[v-shex-msft]

Hi @hassanwasef,

I can't reproduce your issue, it works on my side. I think the issue may related to other applications which connect to azure sql. You can check at the connect pool and try to release resources.

Regards,

Xiaoxin Sheng

[hassanwasef]

Hi, @v-shex-msft this might be the case, I will check all the apps that use the db.  Thank you. 

[v-shex-msft]

Hi @hassanwasef,

Does this works?

Regards,

Xiaoxin Sheng

[hassanwasef]

unfortunately, it didn't work.. I guess the problem is away from the other apps that use the DB. 
When i tried to make a new pbix with just one visual that connects to the same db. it worked fine.. 
on the other hand when i run the pbix with many visuals and reports. it returns the error mentioned. 
so it is power bi issue i believe.. is it possible that power bi opens a sql server pool for each report or visual ? 

[alaeddin14]

Have you tried to increase the "Command timeout in minutes" ? 

Power bi definitely doesn't run the query again for each graph, but when it comes to a remote database, the connections quality also plays a role. 

Cheers,

Alaeddin 

[v-shex-msft]

Hi @hassanwasef,

>>is it possible that power bi opens a sql server pool for each report or visual ? 

No, it only use one pool, it will auto release the resource after get data. (it not keep the live connection)

>>When i tried to make a new pbix with just one visual that connects to the same db. it worked fine.. 

Sorry for that, I also not know what will caused the issue.

Regards,

Xiaoxin Sheng

[vanessafvg]

the first place i would start is by trying to reduce what i am bring back if possible, ie. are you bringing back more than you should, just bring back the columns and range of data that you need.  

Also check things like data types, i am not sure how much control you have over that, but obviously bring back less might help with the timeout.   If you have huge text columns that dont need to be text or could be smaller that will have an impact.

Are you doing a lot of tranformations on the data, try to do that in SQl as much as possible. i.e server side

Do you have indexes on your tables?

Is it direct query or imported?

Is it intermittent or does it do this every time?

[hassanwasef]

Well,  Thank you for your advice.. 
yes all calculations are done in SQL, i just receive the final raw data to be used directly without any calculations nor measure on power bi. 
No indexes yet, but i thought of adding indexes to improve performance since it takes ages to draw visuals and filter it (as i use the direct query method).

Well, I have changed something that would have been the cause of this issue. as this problem happened after downgrading the Azure SQL Plan from the Premuim one to the standard S1, this means reducing the OLTP of the SQL DB for less DTUs.
Would that affect the pool size? and cause such a issue ?I'm not sure..