Forum Discussion
Power BI Desktop Timeout expired.
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
9 Replies
- vanessafvg
Community Champion
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?
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..
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
Hi, Anonymous this might be the case, I will check all the apps that use the db. Thank you.
Anonymousalaeddin14 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?
